Better way of writing this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Better way of writing this query

Author

Topic

SQLdodo
Starting Member

4 Posts

Posted - 2012-08-16 : 19:36:21

I am new to tsql but I'm wondering is there a better way to write this query.
Logic is as follows: Grades are from 1-5 and I have to count the number of grades for every Subject, for example

For subject Mathematics there are 2 one's and 3 fives etc... and at the end I've to make Averages for every subject but I have to start counting grades without grade '1'

here is the example code

CREATE TABLE [dbo].[Grades](
[GradesID] [int] IDENTITY(1,1) NOT NULL,
[SubjectID] [int] NOT NULL,
[Grade] [int] NOT NULL)

CREATE TABLE [dbo].[Subjects](
[SubjectId] [int] IDENTITY(1,1) NOT NULL,
[SubjectCode] [int] NOT NULL,
[SubjectName] [varchar](100) NOT NULL)
GO
SET IDENTITY_INSERT Subjects ON;
--SET IDENTITY_INSERT Subjects Off;

INSERT INTO Subjects (SubjectId,SubjectCode,SubjectName) values ( 1,1, 'CS');
INSERT INTO Subjects (SubjectId,SubjectCode,SubjectName) values (2, 2, 'Mathematics')
INSERT INTO Subjects (SubjectId,SubjectCode,SubjectName) values (3, 3, 'Phisics')
INSERT INTO Subjects (SubjectId,SubjectCode,SubjectName) values (4, 4, 'Chemistry')
INSERT INTO Subjects (SubjectId,SubjectCode,SubjectName) values (5, 5, 'Biology')
select * from Subjects

SET IDENTITY_INSERT Grades On;
SET IDENTITY_INSERT Grades Off;

INSERT INTO Grades (GradesID, SubjectID,Grade) SELECT 1, 1, 1
INSERT INTO Grades (GradesID, SubjectID,Grade)SELECT 2, 2, 2
INSERT INTO Grades (GradesID, SubjectID,Grade)SELECT 3, 3, 3
INSERT INTO Grades (GradesID, SubjectID,Grade)SELECT 4, 4, 1
INSERT INTO Grades (GradesID, SubjectID,Grade)SELECT 5, 1, 1
INSERT INTO Grades (GradesID, SubjectID,Grade)SELECT 6, 2, 1
INSERT INTO Grades (GradesID, SubjectID,Grade)SELECT 7, 3, 1
INSERT INTO Grades (GradesID, SubjectID,Grade)SELECT 8, 4, 2
INSERT INTO Grades (GradesID, SubjectID,Grade)SELECT 9, 1, 3
INSERT INTO Grades (GradesID, SubjectID,Grade)SELECT 10, 2, 4
INSERT INTO Grades (GradesID, SubjectID,Grade)SELECT 11, 3, 5
INSERT INTO Grades (GradesID, SubjectID,Grade)SELECT 13, 4, 5
select * from Grades

--=============================

WITH GradesAverage(SubjectName, Num1, Num2, Num3, Num4, Num5, Average) AS
(
SELECT Subjects.SubjectName, COUNT(*) OVER(PARTITION BY Grades.SubjectID) AS Num1, '' AS Num2, '' AS Num3, '' AS Num4, '' AS Num5, '' AS Average
FROM Subjects INNER JOIN Grades ON Subjects.SubjectID = Grades.SubjectID
WHERE ([Grades].[Grade] = 1)
UNION ALL
SELECT Subjects.SubjectName, '' AS Num1, COUNT(*) OVER(PARTITION BY Grades.SubjectID) AS Num2, '' AS Num3, '' AS Num4, '' AS Num5, '' AS Average
FROM Subjects INNER JOIN Grades ON Subjects.SubjectID = Grades.SubjectID
WHERE ([Grades].[Grade] = 2)
UNION ALL
SELECT Subjects.SubjectName, '' AS Num1, '' AS Num2, COUNT(*) OVER(PARTITION BY Grades.SubjectID) AS Num3, '' AS Num4, '' AS Num5, '' AS Average
FROM Subjects INNER JOIN Grades ON Subjects.SubjectID = Grades.SubjectID
WHERE ([Grades].[Grade] = 3)
UNION ALL
SELECT Subjects.SubjectName, '' AS Num1, '' AS Num2, '' AS Num3, COUNT(*) OVER(PARTITION BY Grades.SubjectID) AS Num4, '' AS Num5, '' AS Average
FROM Subjects INNER JOIN Grades ON Subjects.SubjectID = Grades.SubjectID
WHERE ([Grades].[Grade] = 4)
UNION ALL
SELECT Subjects.SubjectName, '' AS Num1, '' AS Num2, '' AS Num3, '' AS Num4, COUNT(*) OVER(PARTITION BY Grades.SubjectID) AS Num5, '' AS Average
FROM Subjects INNER JOIN Grades ON Subjects.SubjectID = Grades.SubjectID
WHERE ([Grades].[Grade] = 5)

UNION ALL
SELECT Subjects.SubjectName, '' AS Num1, '' AS Num2, '' AS Num3, '' AS Num4, '' AS Num5, AVG([Grades].[Grade]) OVER(PARTITION BY Grades.SubjectID) AS Average
FROM Subjects INNER JOIN Grades ON Subjects.SubjectID = Grades.SubjectID
WHERE ([Grades].[Grade] between 2 and 5)
)

SELECT SubjectName, MAX(Num1) AS Num1, MAX(Num2) AS Num2, MAX(Num3) AS Num3, MAX(Num4) AS Num4, MAX(Num5) AS Num5, MAX(Average) AS Average
FROM GradesAverage
GROUP BY SubjectName

GO

drop table Grades
drop table Subjects

and the result of this query is

Subject | Num1 | Num2 | Num3 | Num4 | Num5 | Averages
Chemistry 1 1 0 0 1 3
CS 2 0 1 0 0 3
Mathematics 1 1 0 1 0 3
Phisics 1 0 1 0 1 4

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-16 : 21:08:33

I must admit that I did not follow the logic you are trying to implement - so I am mechanically translating your query into a single select as shown below.

However, you may want to look into at least one thing: When you take the average, you might want to get the fractional part also; hence my multiplication by 1.0 shown in red. If you don't want that, of course don't multiply.

SELECT
	s.SubjectName,
	SUM(CASE WHEN s.SubjectId = g.SubjectId AND g.Grade = 1 THEN 1 ELSE 0 END) AS Num1,
	SUM(CASE WHEN s.SubjectId = g.SubjectId AND g.Grade = 2 THEN 1 ELSE 0 END) AS Num2,
	SUM(CASE WHEN s.SubjectId = g.SubjectId AND g.Grade = 3 THEN 1 ELSE 0 END) AS Num3,
	SUM(CASE WHEN s.SubjectId = g.SubjectId AND g.Grade = 4 THEN 1 ELSE 0 END) AS Num4,
	SUM(CASE WHEN s.SubjectId = g.SubjectId AND g.Grade = 5 THEN 1 ELSE 0 END) AS Num5,
	AVG(CASE WHEN s.SubjectId = g.SubjectId AND g.Grade BETWEEN 2 AND 5 THEN 1.0*g.Grade END) AS Average
FROM
	Subjects s
	INNER JOIN Grades g
		ON  s.SubjectID = g.SubjectID
GROUP BY
	s.SubjectName;

SQLdodo
Starting Member

4 Posts

Posted - 2012-08-17 : 05:03:15

Well you got the logic right and your code is much cleaner and easier to read than mine. Kudos for that.

Logic is to count the grades from 1-5 of each subject and store them into the columns Num1-Num5, for example CS has 3 grades (1,1,3) and result should be like this Num1 = 2 (because there are two 1's) an Num3 = 1 (because there is only one grade and it is number 3) and at the end it has to do Average of those grades but without taking into account the 1's so for CS Subject it will take only grade 3.

And that's pretty much it, nothing special.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources