question

Mamzy avatar image
Mamzy asked

how would i find the sum of a group from another sum of a group eg.

select learner, sum (GLH) from LearnerTable Group By Learner OUTPUT Learner GLH John 100 Harry 200 Fred 300 Ted 350 Now I want to Add all the GLH that are < 300, between 300-340 and 350+ so output will look like GLH N# of learners under 300 2 300-340 1 350+ 1
sql-server-2008querysum
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
Try this: DECLARE @LearnerTable TABLE ( Learner VARCHAR(20) , GLH INT ) INSERT INTO @LearnerTable VALUES ( 'John', 100 ), ( 'Harry', 200 ), ( 'Fred', 300 ), ( 'Ted', 350 ); WITH LearnerSumGLH AS ( SELECT Learner , SUM(GLH) AS SumGLH FROM @LearnerTable GROUP BY Learner ) SELECT CASE WHEN SumGLH < 300 THEN 'Under 300' WHEN SumGLH >= 350 THEN '350' ELSE '300-350' END AS GLH , COUNT(*) AS NumLearners FROM LearnerSumGLH GROUP BY CASE WHEN SumGLH < 300 THEN 'Under 300' WHEN SumGLH >= 350 THEN '350' ELSE '300-350' END However, if you have to have the data in that order, then that's a little more complicated: DECLARE @LearnerTable TABLE ( Learner VARCHAR(20) , GLH INT ) INSERT INTO @LearnerTable VALUES ( 'John', 100 ), ( 'Harry', 200 ), ( 'Fred', 300 ), ( 'Ted', 350 ); WITH LearnerSumGLH AS ( SELECT Learner , CASE WHEN SUM(GLH) < 300 THEN 'Under 300' WHEN SUM(GLH) >= 350 THEN '350+' ELSE '300-350' END AS SumGLH , CASE WHEN SUM(GLH) < 300 THEN 1 WHEN SUM(GLH) >= 350 THEN 3 ELSE 2 END AS GLHOrder FROM @LearnerTable GROUP BY Learner ) SELECT SumGLH AS GLH , COUNT(*) AS NumLearners FROM LearnerSumGLH GROUP BY SumGLH , GLHOrder ORDER BY GLHOrder In both answers, what we're doing is using a [WITH statement to create a CTE (Common Table Expression)][1]. This allows us to create an intermediate pseudo-table which holds the results of the initial select statement (in my case, from a table variable, in your case from a base table). This particular SELECT statement differs from yours in that I'm transforming the data into something that is more obviously related to the output you require - replacing GLH values with your grade band labels, for example - and (in the second example) also setting up a column for sorting based on the same cases. Note that the second example brings the data transformation up into the CTE declaration to make the final SELECT statement easier to read. I could also have done this with the first example... [1]: http://msdn.microsoft.com/en-us/library/ms175972.aspx
13 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mamzy avatar image Mamzy commented ·
Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'ELSE'. THIS IS THE ERROR MESSAGE I RECIEVE
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
What is your query?
0 Likes 0 ·
Mamzy avatar image Mamzy commented ·
with LearnerSumGLH AS (select [L03] AS LEARNER, SUM (A32) AS TOTALGUIDED FROM [LSC_MI_db_pub].[dbo].[SILR1112_AIMS_SN13] GROUP BY [L03]) SELECT CASE WHEN TOTALGUIDED < 280 THEN 'UNDER 300' ELSE '280-350' ELSE '350-400' ELSE '400-450' END AS [A32], COUNT (*) AS NMBEROFLEARNERS FROM LEARNERSUMGLH GROUP BY CASE WHEN TOTALGUIDED < 280 THEN 'UNDER 300' ELSE '280-350' ELSE '350-400' ELSE '400-450' END
0 Likes 0 ·
Mamzy avatar image Mamzy commented ·
THANKS FOR YOUR HELP BY THE WAY
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Ah, I see what you've done. You've managed to get the CASE block slightly muddled. The SELECT ... CASE ... statement works like this: CASE WHEN THEN ... WHEN THEN ... .... ELSE ... END or CASE WHEN THEN ... WHEN THEN ... ... ELSE ... END In your case, I would suggest that you need to use: CASE WHEN TotalGuided < 280 THEN 'Under 280' WHEN TotalGuided >= 280 AND TotalGuided < 350 THEN '280-350' WHEN TotalGuided >= 350 AND TotalGuided < 400 THEN '350-400' WHEN TotalGuided >=400 AND TotalGuided < 450 THEN '400-450' ELSE '450+' END
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.