查询2 COUNTS时出现GROUP BY语句错误

Question

我正在尝试查询表中每个组的注册用户和注册用户的计数。

然而，我不断收到重复的组名、注册和注册。我需要它只是唯一的组名，在其相应的列中有计数。我是个新手，3天来我一直在思考这个问题。如有任何协助，我们将不胜感激。下面是我最接近的，但仍然有重复的组名。

WITH CTE_GROUP as (
SELECT DISTINCT [GROUP] AS GROUPS
FROM TABLE1
WHERE AUTH_PAGE = 'X' AND CAST(CreationDate AS DATE) >= '2022-04-01' AND CAST(CreationDate AS DATE) < '2022-05-01' AND EMAIL IS NOT NULL AND EMAIL <> '' AND EMAIL NOT LIKE '[e-mail]' OR CAST(CreationDate AS DATE) >= '2022-04-01' AND CAST(CreationDate AS DATE) < '2022-05-01' AND EMAIL IS NOT NULL AND EMAIL <> '' AND EMAIL NOT LIKE '[e-mail]'
GROUP BY [GROUP]
),
CTE_REGISTERED as (
SELECT [GROUP], COUNT (*) AS REGISTERED
FROM TABLE1
WHERE CAST(CreationDate AS DATE) >= '2022-04-01' AND CAST(CreationDate AS DATE) < '2022-05-01' AND EMAIL IS NOT NULL AND EMAIL <> '' AND EMAIL NOT LIKE '[e-mail]'
GROUP BY [GROUP]
),
CTE_ENROLLED as (
SELECT [GROUP], COUNT (*) AS ENROLLED
FROM TABLE1
WHERE AUTH_PAGE = 'X' AND CAST(CreationDate AS DATE) >= '2022-04-01' AND CAST(CreationDate AS DATE) < '2022-05-01' AND EMAIL IS NOT NULL AND EMAIL <> '' AND EMAIL NOT LIKE '[e-mail]'
GROUP BY [GROUP]
)
SELECT DISTINCT GROUPS, REGISTERED, ENROLLED
FROM CTE_GROUP, CTE_REGISTERED, CTE_ENROLLED

我的结果是这样的。

GROUPS               REGISTERED       ENROLLED
CompanyA              3                 2
CompanyB              3                 3
CompanyA              3                 2
CompanyB              3                 3

我正在寻找的结果应该是

GROUPS               REGISTERED       ENROLLED
CompanyA              3                 2
CompanyB              3                 3

感谢

Answer 1

我相信你需要一个">条件聚合"；在单个查询中：

SELECT
[group]
, count(*) AS registered
, count(CASE WHEN AUTH_PAGE = 'X' THEN 1 END) AS enrolled
FROM table1
WHERE CreationDate >= '20220401'
AND CreationDate < '20220501'
AND EMAIL NOT LIKE '[e-mail]'
GROUP BY
[group]
;

本质上，您在聚合函数(如count())中使用case expression，通过这种方式，您的计数继承了"；条件"；通过该CCD_ 3定义。

还要注意，您不需要将这些[CreateonDate]转换为"；日期"；使where子句谓词起作用。所有与日期/时间相关的数据类型都可以与日期文字进行比较。此外，在SQL Server中；"最安全"；日期文字只是YYYYMMDD格式。

Answer 2

您的结果集基本上是一个CROSS JOIN，因为在三个CTE之间没有JOIN条件。

试试这个：

SELECT 
* 
FROM 
CTE_GROUP AS [Groups] 
INNER JOIN CTE_Registered AS [Registered] ON [Groups].[GROUPS] = [Registered].[GROUP]
INNER JOIN CTE_ENROLLED AS [Enrolled] ON [Groups].[GROUPS] = [Enrolled].[GROUP]

通常，当您处理来自多个表(无论它们是实际的物理表、视图还是内存表)的数据时，您都需要让数据库引擎知道这些数据是如何关联的。您可以通过创建关系(使用主键和外键)以及使用JOIN条件指示查询中的引擎来完成此操作。表可以通过多个字段进行关联，在这种情况下，可以像WHERE子句一样，在JOIN条件中将它们串在一起。

请注意，执行JOIN有多种方法，结果将取决于您是否使用正确的类型。在我提供的示例查询中，我使用的是INNER JOIN，它确保您只从JOIN中返回两个表中都存在的结果。