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

 0 ``````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 `````` more ▼ asked Sep 17 '12 at 02:34 PM in Default Mamzy 10 ● 2 ● 4 ● 4 ThomasRushton ♦ 33.4k ● 14 ● 20 ● 44 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 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). 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... more ▼ answered Sep 17 '12 at 02:44 PM ThomasRushton ♦ 33.4k ● 14 ● 20 ● 44 Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'ELSE'.THIS IS THE ERROR MESSAGE I RECIEVE Sep 17 '12 at 02:58 PM Mamzy What is your query? Sep 17 '12 at 03:00 PM ThomasRushton ♦ ``````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 `````` Sep 17 '12 at 03:08 PM Mamzy THANKS FOR YOUR HELP BY THE WAY Sep 17 '12 at 03:09 PM Mamzy 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 `````` Sep 17 '12 at 04:01 PM ThomasRushton ♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x362
x23

asked: Sep 17 '12 at 02:34 PM

Seen: 626 times

Last Updated: Sep 18 '12 at 02:12 PM