SQL Server“无法对包含聚合或子查询的表达式执行聚合功能”，但是Sybase可以 [英] SQL Server "cannot perform an aggregate function on an expression containing an aggregate or a subquery", but Sybase can

问题描述

这个问题之前已经讨论过，但是没有一个答案能解决我的特定问题，因为我正在处理内部和外部selects中不同的where子句。该查询在Sybase下执行得很好，但是在SQL Server下执行时，此标题的标题中有错误。查询很复杂，但查询的总体轮廓为：

This issue has been discussed before, but none of the answers address my specific problem because I am dealing with different where clauses in the inner and outer selects. This query executed just fine under Sybase, but gives the error in the title of this post when executed under SQL Server. The query is complicated, but the general outline of the query is:

select sum ( t.graduates -
    ( select sum ( t1.graduates )
      from table as t1
      where t1.id = t.id and t1.group_code not in ('total', 'others' ) ) )
from table as t
where t.group_code = 'total'

以下描述了我的情况尝试解决：

The following describes the situation I am trying to resolve:

• 所有组代码均代表种族，但总数和其他除外


• 组代码总计代表所有种族的毕业生总数


• 但是，缺少多种族竞赛，因此种族的毕业生人数可能不等于毕业生总数


• 缺少的数据就是需要计算的数据

• all group codes represent races except for 'total' and 'others'
• group code 'total' represents the total graduates of all races
• however, multi-race is missing, so the race graduate counts may not add up to the total graduate counts
• this missing data is what needs to be calculated

无论如何，是否存在使用派生表或联接重写的数据以获得相同的结果？

Is there anyway to rewrite this using derived tables or joins to get the same results?

更新：：我创建了样本数据和3我特定问题的解决方案（受sgeddes影响2）。我添加的内容涉及将相关子查询移动到FROM子句中的派生表。谢谢您的帮助！

Update: I created sample data and 3 solutions to my specific problem (2 influenced by sgeddes). The one that I added involves moving the correlated subquery to a derived table in the FROM clause. Thanks for the help guys!

推荐答案

一种选择是将子查询放入 LEFT JOIN ：

One option is to put the subquery in a LEFT JOIN:

select sum ( t.graduates ) - t1.summedGraduates 
from table as t
    left join 
     ( 
        select sum ( graduates ) summedGraduates, id
        from table  
        where group_code not in ('total', 'others' )
        group by id 
    ) t1 on t.id = t1.id
where t.group_code = 'total'
group by t1.summedGraduates 

也许更好的选择是将 SUM 与 CASE ：

Perhaps a better option would be to use SUM with CASE:

select sum(case when group_code = 'total' then graduates end) -
    sum(case when group_code not in ('total','others') then graduates end)
from yourtable

同时使用SQL Fiddle演示

这篇关于SQL Server“无法对包含聚合或子查询的表达式执行聚合功能”，但是Sybase可以的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！