Summarize Data for Report using T-SQL Case Statement

Question

I want to create a simple summary report in Reporting Services using age, account and age group as follows:

SELECT AGE,COUNT(ACCOUNT)AS TOTALCASES, 
       'AGEGRP' =CASE WHEN AGE <=5 THEN 'AGE 0 TO 5'
                      WHEN AGE >=6 THEN 'AGE 6 AND OLDER'
                 END
FROM MAIN 
GROUP BY 'AGEGRP'

When I run this in SQL Server Management Studio, I receive error message:

Msg 164, Level 15, State 1, Line 1 Each GROUP BY expression must contain 
at least one column that is not an outer reference.

Can someone suggest a way to produce summarized data, counting account number, summarizing by age 0 to 5 and age 6 and older?

Answer 1

you can't have "age" in the select list if you group by AGEGRP

try:

DECLARE @YourTable table (age int, account int)
insert into @YourTable values (1,40)
insert into @YourTable values (2,40)
insert into @YourTable values (3,40)
insert into @YourTable values (4,40)
insert into @YourTable values (5,40)
insert into @YourTable values (6,40)
insert into @YourTable values (7,40)
insert into @YourTable values (8,40)

SELECT
    COUNT(ACCOUNT)AS TOTALCASES, AGEGRP
    FROM (SELECT
              AGE,ACCOUNT, CASE
                               WHEN AGE <=5 THEN 'AGE 0 TO 5'
                               WHEN AGE >=6 THEN 'AGE 6 AND OLDER'
                           END AS AGEGRP
              FROM @YourTable 
         )dt
    GROUP BY AGEGRP

OUTPUT:

TOTALCASES  AGEGRP
----------- ---------------
5           AGE 0 TO 5
3           AGE 6 AND OLDER

(2 row(s) affected)

Answer 2

Either you do an inner query, like KM shows, or you repeat the expression you want to group by:

SELECT 
  AGE,
  COUNT(ACCOUNT) AS TOTALCASES, 
  CASE 
    WHEN AGE <=5 THEN 'AGE 0 TO 5'
    ELSE 'AGE 6 AND OLDER'
  END AS AGEGRP
FROM 
  MAIN 
GROUP BY 
  CASE 
    WHEN AGE <=5 THEN 'AGE 0 TO 5'
    ELSE 'AGE 6 AND OLDER'
  END

Answer 3

It's impossible to have AGE in the final result set. I feel you are mixing two requests together. Taking KM's solution, You can either have the inner result, or the outer result without the AGE column.

EDIT: KM just edited his reply, so do I:) Anyway, I was referencing the following two results:

1. select age, (case ... end) as agegroup from main
2. select agegroup, count(*) as cases from (select age, (case ... end) as agegroup) t group by agegroup