使用T-SQL案例声明汇总报告数据

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

我想在Reporting Services中使用年龄,帐户和年龄组创建一个简单的摘要报告,如下所示:

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:

当我在SQL Server Management Studio中运行它时,收到错误消息:

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?

有人可以建议一种方法来生成汇总数据,计算帐号,按年龄0到5岁以及6岁及以上的人员进行汇总吗?

3 个解决方案

#1

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

如果按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)

#2

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

您可以执行内部查询(如KM显示),也可以重复要分组的表达式:

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

#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.

在最终结果集中不可能有AGE。我觉得你在一起混合两个请求。采用KM的解决方案,您可以获得内部结果,也可以获得没有AGE列的外部结果。

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

编辑:KM刚编辑了他的回复,我也是:) :)无论如何,我引用了以下两个结果:

select age, (case ... end) as agegroup from main

选择年龄,(案例......结束)作为年龄组从main

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

从年龄组中选择年龄组,计数(*)作为(选择年龄,(病例...结束)作为年龄组)t组的病例

#1