x

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
more ▼

asked Sep 17 '12 at 02:34 PM in Default

Mamzy gravatar image

Mamzy
10 2 4 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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 gravatar image

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 <expression>
    WHEN <value 1> THEN ...
    WHEN <value 2> THEN ...
    ....
    ELSE ...
END

or

CASE 
    WHEN <logical expression 1> THEN ...
    WHEN <logical expression 2> 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 ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

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.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

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