Selecting COUNTS in SQL

[gbhs]

Hi
I'm a newbie in SQL and for scores whose credit is greater than 2, I want to select students with scores of 10 or more .Here is what I wrote and Im having errors(See table below):

SELECT Student,COUNT(Score)>=10 AS NumPasses
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     9

I want the query results to be as below

  Student   NumberOfPasses  
    Tomy    2   
    Dick    1   
    Hary    0   

Can someone help
Thanks

[pritaeas]

You are using an expression in the SELECT. If you move that to a HAVING (or WHERE depending on your needs) clause, you will get the results you need.

[gbhs]

Hi
I have Modified it 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 (with 0 count) to be included in the result.
can you help?
thanks

[pritaeas]

You'll need to do that with a LEFT JOIN or a UNION. As it is now Hary is not in the results.

[gbhs]

I'd be grateful if you help me. I am new to SQL and VB.net

[JorgeM]

Here's a hint.. Create a student table listing each student. Left join the tables and use the ISNULL function to replace the null value for Harry with a '0'.

[IIM]

The only way is separate this table into 2 tables and then use it.The first table contains user_id,user_name and second one contains user_id,score,credit.Now use join as suggested by pritaeas.

Select u.user_name,count(c.score) from table1 as t LEFT JOIN table2 as c GROUP BY u.user_name,c.score 
HAVING c.credit >2

[iamthwee]

Doesn't dotnet come with a polished query builder (drag and connect tables?)

[JorgeM]

Yes, SQL Management Studio (version 2012 now powered by Visual Studio) does provide the SQL GUI designer. It would be easy to come up with the SQL code using that method, but not using the GUI is an opportunity at learning how to write JOINs by hand...

[M.Waqas Aslam]

select Distinct t.Student ,(Select count(scores) from table where student = t.Student)
From Table t
where credit > 2 and credit > 10

may be this will help you .
Regards

[anupamduttabd]

SELECT Student,COUNT(Score) AS NumPasses
WHERE Credit>2 and Score>=10
GROUP BY Student

[cgyrob]

Try This

1. Select Student,sum(case When Credit > 2 and Score > 10 Then 1 else 0 end) as NumPasses    
2. From MyTable

[cgyrob]

Forgot the group by

Select Student,sum(case When Credit > 2 and Score > 10 Then 1 else 0 end) as NumPasses    
From MyTable
Group by Student