SQL query for SQL Server

Question

I have 3 tables students, class and grades. I need to make a query that will count how many specific grades are for specific class. And also calculate average grade for specific class where grade 1 is not included (grades are 1,2,3,4,5).

Columns for the query should look like this:

Class  | Grade 1 |   Grade 2  |   Grade 3  |   Grade 4  |  Grade 5  |   Average (except 1)

I know how to get result for one specific grade:

select C.ClassName, count(G.Grade)
from Classes C, Grades G
where G.ClassesID = C.ClassesID and G.Grade = 1
group by C.ClassName

but how do I make a query to get all columns at once?

this screams homework! And the use of implied syntax is a SQL antipattern, do not learn to use it, learn to correctly use explicit joins. — HLGEM
– HLGEM, Commented Sep 22, 2011 at 17:25
It's not really a homework. And sorry if it's a stupid question I didn't do anything in sql for long time. I would apreciate if someone can help me with this one — Sima
– Sima, Commented Sep 22, 2011 at 17:30
I told you where to look for your answer. Try to make CASE work and comeback if you can't figure it out. — HLGEM
– HLGEM, Commented Sep 22, 2011 at 17:33
It is not assignment from educational institution and I will not get a grade for it — Sima
– Sima, Commented Sep 22, 2011 at 17:47

Simon · Accepted Answer · 2011-09-22 18:53:59Z

1

If your SQL Server is new enough (2005 or newer), PIVOT can save you a lot of trouble here.

SELECT ClassName, [1], [2], [3], [4], [5], ([1] + [2] + [3] + [4] + [5])/5 as [Average Count]
FROM
(
    select C.ClassName, G.Grade
    from Classes C
    join Grades G on G.ClassesID = C.ClassesID
) AS source
PIVOT
(
    count(G.Grade)
    FOR Grade IN ([1], [2], [3], [4], [5])
) as pvt

answered Sep 22, 2011 at 18:53

Simon

1,2071 gold badge7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sima Over a year ago

I get some kind of errors. I never used PIVOT before so I'm not really sure how it works but I will check it out and try to figure out why is not working

Chains Over a year ago

@Sima -- FWIW -- PIVOT would be a much better solution for you than Tom's answer that you accepted. I know it's a bit wierd-looking at first, but you won't regret learning how to do this approach.

Tom · Accepted Answer · 2011-09-22 18:05:09Z

I would try SUBQUERY. If you use MSSQL you can try this:

SELECT C.ClassName, 
--Grade 1
(SELECT COUNT(G1.Grade) 
FROM Classes C1, Grades G1 
WHERE G1.ClassesID = C1.ClassesID and G1.Grade = 1 and C1.ClassName = C.ClassName),
--Grade 2
(SELECT COUNT(G2.Grade) 
FROM Classes C2, Grades G2 
WHERE G2.ClassesID = C2.ClassesID and G2.Grade = 2 and C2.ClassName = C.ClassName),    
-- Grade 3
(SELECT COUNT(G3.Grade) 
FROM Classes C3, Grades G3 
WHERE G3.ClassesID = C3.ClassesID and G3.Grade = 3 and C3.ClassName = C.ClassName),
--Grade 4
(SELECT COUNT(G4.Grade) 
FROM Classes C4, Grades G4 
WHERE G4.ClassesID = C4.ClassesID and G4.Grade = 4 and C4.ClassName = C.ClassName),
--Grade 5
(SELECT COUNT(G5.Grade) 
FROM Classes C5, Grades G5 
WHERE G5.ClassesID = C5.ClassesID and G5.Grade = 5 and C5.ClassName = C.ClassName)
-- Average (except 1)
(SELECT SUM(GA.Grade) / COUNT(GA.Grade) 
FROM Classes CA, Grades GA 
WHERE GA.ClassesID = CA.ClassesID and GA.Grade NOT IN (1) and CA.ClassName = C.ClassName)

FROM Classes C, Grades G
WHERE G.ClassesID = C.ClassesID
GROUP BY C.ClassName

Aliases for the tables are not necessary. -- Means comment.

Collectives™ on Stack Overflow

SQL query for SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related