Summarize Data for Report using T-SQL Case Statement
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
?
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)
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
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:
- select age, (case ... end) as agegroup from main
- select agegroup, count(*) as cases from (select age, (case ... end) as agegroup) t group by agegroup
精彩评论