SQL query for SQL Server

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 开发者_StackOverflow中文版make a query to get all columns at once?

---

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

---

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.