0

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

8
Contributors
12
Replies
46
Views
4 Years
Discussion Span
Last Post by cgyrob
Featured Replies
  • 2

    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'. Read More

0

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.

Edited by pritaeas

0

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

0

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

2

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'.

Edited by JorgeM

0

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
0

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...

Edited by JorgeM

0
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

0

Try This

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

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
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.