新列的SQL不同分组依据

Question

我已经尝试了所有的搜索来尝试解决这个问题，要么是因为我没有正确搜索

我有一个带有子查询的查询，中有两个联合查询

示例：-

SELECT name, status , SUM(TestCount) as ‘TestCount’
FROM(
select e1.reference as reference, 
e1.name as name,
e1.status as status,
, SUM(CASE WHEN e1.date IS NOT NULL THEN 1 ELSE 0 END) as TestCount
FROM dbo.table1 as e1

WHERE e1.status IN('A','B','C')
GROUP BY e1.name, e1.status
UNION ALL
select e2.reference as reference, 
e2.name as name,
e2.status as status,
SUM(CASE WHEN e2.date IS NOT NULL THEN 1 ELSE 0 END) as TestCount
FROM dbo.table2 as e2

WHERE e2.status IN('A','B','C')
GROUP BY e2.name, e2.status
) t
GROUP BY Name, Status

我想在我的子查询中添加一个SUM CASE WHEN列，然后在顶部将其拉出，只按名称分组，而不按状态分组。(因此，如果一个人的名字在5种不同的状态下出现了5次，只需为他们的名字显示5次相同的数字(。问题是，如果我使用WHERE子句(内部(或HAVING外部，它会筛选出具有最后一列的SUM CASE WHEN条件的状态

例如

状态：-

ABCDE-这里是当来自时的SUM情况

我想使用WHERE只显示a、B、C、D，但我想要E.的计数

希望这是有意义的

根据建议编辑：-

SUM(CASE WHEN MONTH(Field1Date) = MONTH(GETDATE()) AND YEAR(Field1Date) = YEAR(GETDATE())  AND TypeCode IN('A', 'B', 'C')  THEN 1
ELSE 0 END) OVER (PARTITION BY Name)  as full_count

这会导致：-

Msg 8120, Level 16, State 1, Line 1
Column 't.Field1Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 't.Field1Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 't.TypeCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 't.TypeCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 't.TypeCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

这样做会将错误减少到2:-

SUM(CASE WHEN MONTH(Field1Date) = MONTH(GETDATE()) AND YEAR(Field1Date) = YEAR(GETDATE())  AND TypeCode IN('A', 'B', 'C')  THEN 1
ELSE 0 END) OVER (PARTITION BY Name, Field1Date, TypeCode)  as full_count

Msg 8120, Level 16, State 1, Line 2
Column 't.Field1Date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 't.TypeCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

我可以通过将最后两个错误添加到GROUP by来消除它们，但我不想从一开始就对它们进行GROUP by，如果这有意义的话？

如果我将它们添加到GROUP BY中以克服错误，则SUM始终为0，因为内部查询中的WHERE语句会过滤掉计数所在的所有"状态">

对不起，我应该说这是SQL Server。SSMS 中的TSQL

Answer 1

您可能正在寻找SUM OVER来获得一个组的总数。您还没有告诉我们您正在使用哪个DBMS，但这是许多DBMS中可用的标准SQL函数。

我已将您的SUM(CASE WHEN date IS NOT NULL THEN 1 ELSE 0 END)简化为简单的COUNT(date)。我还从内部查询中删除了聚合，因为无论如何都必须在主查询中进行聚合。

SELECT
name,
status,
COUNT(date) as partial_count,
SUM(COUNT(date)) OVER (PARTITION BY name) as full_count
FROM
(
SELECT name, status, date FROM dbo.table1 WHERE status IN ('A', 'B', 'C')
UNION ALL
SELECT name, status, date FROM dbo.table2 WHERE status IN ('A', 'B', 'C')
) t
GROUP BY name, status
ORDER BY name, status;