COUNT and GROUP BY Problem

[gbhs]

Hi I used the table below in writng this query SELECT Student,COUNT(Score)>=10 AS NumberOfPasses WHERE Credit>2 GROUP BY Student

Student	Credit	Score
Tomy	3	11
Tomy	3	8
Tomy	2	13
Tomy	3	14
Dick	3	8
Dick	3	14
Dick	2	12
Dick	3	7
Hary	3	8
Hary	3	7
Hary	2	11
Hary	3

The result I need is

Student	NumberOfPasses
Tomy	2
Dick	1
Hary	0

But Im not getting it. Error occurs.
Can someone help
Thanks

 

[jmcilhinney]

That whole ">= 10" stuff is not valid syntax. Do you realise that you haven't actually told us what it is that you're trying to achieve? You are expecting us to work out what it is that want from code that doesn't work. Next time, please provide a FULL and CLEAR explanation of EXACTLY what it is that you're trying to achieve. I'm guessing that what you want to do is only include records in the query if the Credit is greater than 2 and the Score is greater than or equal to 10. That's what the WHERE clause is for. If you want to filter on any column then you do it in the WHERE clause. I think what you want is:

SELECT Student, COUNT(*) AS NumberOfPasses
WHERE Credit > 2 AND Score >= 10
GROUP BY Student

 

[gbhs]

Sorry the error was mine. I omitted the FROM Clause. Assume table name is MyTable
I want to get the counts of scores that are 10 or more and whose credit value is greater than 2 (for all students , even if the count is 0)
I have modified my query thus

SELECT Student,COUNT(Score) AS NumberOfPasses
FROM MyTable
WHERE Score >=10
GROUP BY Student,Credit
HAVING Credit >2

and gotten

Student	NumberOfPasses
Tomy	2
Dick	1

but I want Hary's count (which is 0) to be included in this result.
Thanks

 

[jmcilhinney]

Sorry the error was mine. I omitted the FROM Clause.

Oops, so did I as I just copied your code and edited it.

I want Hary's count (which is 0) to be included in this result.

In that case I think that you'd have to join the data with itself. Off the top of my head, something like this:

SELECT A.Student, ISNULL(NumberOfPasses, 0) AS NumberOfPasses
FROM
(
    SELECT DISTINCT Student
    FROM MyTable
) A LEFT OUTER JOIN
(
    SELECT Student, COUNT(*) AS NumberOfPasses
    FROM MyTable
    WHERE Credit > 2 AND Score >= 10
    GROUP BY Student
) B ON A.Student = B.Student

The outer join ensures that you get every Student value and the ISNULL will return zero when there is no record matching a Student value in the second query.