sql server programming: Count with compare -- sql server programming

RE: Count with compare

Jens SÃ¼ÃŸmeyer
8/2/2005 12:09:04 AM

select p.PositionID,jobTitle, Count(*) from
Position p left join applicant a on p.PositionID = a.PositionID
Where screentestscore > 75
group by p.positionID,p.JobTitle
--
HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

[quoted text, click to view]

Re: Count with compare

tshad
8/2/2005 12:45:26 AM

"Jens Süßmeyer" <Jens@[Remove_that][for contacting me]sqlserver2005.de>
[quoted text, click to view]

That would work if I was only looking for that variable, but I am looking
for 4 or 5 counts from the same same files (should have mentioned that.

Here is the statement I had put together.

select p.PositionID,jobTitle,Submissions = count(resume),Applications =
count(application),Screened=count(screentest),Qualified=count(screentestscor
e > 75) from Position p left join applicant a on p.PositionID = a.PositionID
group by p.positionID,p.JobTitle

Thanks,

Tom
[quoted text, click to view]

Re: Count with compare

Uri Dimant
8/2/2005 10:06:27 AM

Hi
Try this
SELECT <column lists> FROM Table WHERE
75<(SELECT COUNT(*) FROM Table t WHERE t.PK<=Table.PK)

[quoted text, click to view]

Re: Count with compare

tshad
8/3/2005 9:00:21 AM

[quoted text, click to view]

In the articles I have seen on Count(), it usually says that the parameter
can be either: *, field, expr.

What I am trying to do is Count(expr), but I can't seem to find any examples
of this.

Normally, I would use Count(ScreenTestScore), but I only want
ScreenTestScores > 75, so that was why I tried Count(ScreenTestScore > 75),
which is apparently not legal. So the question is how to do all the Count's
in one statement with 1 or 2 with an expression.

Thanks,

Tom

[quoted text, click to view]

Re: Count with compare

tshad
8/4/2005 4:29:08 PM

[quoted text, click to view]

That seemed to do exactly what I wanted.

I know that the docs say you can do Count(expr), but I couldn't figure out
how to make "ScreenTestScore > 75" into an expression that Count would
accept or use.

Yours work great (as far as I can tell).

Thanks,

Tom
[quoted text, click to view]

Re: Count with compare

Hugo Kornelis
8/4/2005 11:01:13 PM

[quoted text, click to view]
(snip)

Hi Tom,

Try if this gets you what you need:

SELECT p.PositionID,
p.JobTitle,
COUNT(a.ScreenTest) AS Screened,
COUNT(CASE WHEN a.ScreenTestScore > 75 THEN 1 END) AS
Qualified
FROM Position AS p
LEFT JOIN Applicant AS a
ON a.PPositionID = p.PositionID
GROUP BY p.PositionID, p.JobTitle

(untested)

Best, Hugo
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.