mysql - 需要按非数字条件和 GROUP BY 对所有行求和

Question

我有一个表，其中包含一个条件，我需要测试该条件并按组对它进行总计，然后得到等于某个值的组数的总数。

示例:

• 第 1 组条件总数 = 1
• 第 2 组条件总数 = 14
• 第 3 组条件总数 = 5
• 第 4 组条件总数 = 1

等等

我需要计算每组的总数然后得到 = 1 的总组数

这是我想出的用于进行基本组总计的基本代码。 (我正在使用 SUM(IF(condition = true,1,0)) 来构建一个可以用作子查询的查询:

SELECT SUM(IF(`Condition` <> '' AND `Condition` IS NOT NULL,1,0)) AS TotalErrors 
FROM Site_Analytics 
GROUP BY FourID;

当然这是一样的:

SELECT COUNT(*) AS TotalErrors 
FROM Site_Analytics 
GROUP BY FourID 
WHERE `Condition` <> '' AND `Condition` IS NOT NULL;

为每个 GROUP 返回一行，其中包含组总计。然后我需要按 GROUP TOTAL 对 GROUP 进行分组。

无论哪种方式，我都需要获得 = 1(或 2、3、4 等)的组数(FourID)

我正在寻找的最终结果是生成一个报告，其中的组数 = 1, 2,3,4,5,5+

我曾尝试将上述查询用作子查询但没有成功，例如:

SELECT 
SUM((SELECT SUM(IF(`Condition` <> '' AND `Condition` IS NOT NULL,1,0)) FROM Site_Analytics GROUP BY FourID) AS `SitesWith1Error`
FROM SiteLog_Master;

这是我用来获取类似报告的其他总计的查询:

SELECT 
(SELECT COUNT(`FourID`) FROM SiteLog_Master) AS `TotalSites`,
(SELECT COUNT(`Condition`) FROM Site_Analytics WHERE (`Condition` <> '' AND `Condition` IS NOT NULL)) AS `TotalFieldsWithErrors`,
(SELECT COUNT(DISTINCT m.FourID) FROM SiteLog_Master m LEFT JOIN Site_Analytics USING (FourID) WHERE (`Condition` IS NULL OR `Condition` <> '')) AS `TotalSitesWithErrors`,
(SELECT COUNT(`Condition`) FROM Site_Analytics WHERE `Condition` LIKE '%Required%') AS `TotalMissingRequiredFields`,
(SELECT COUNT(DISTINCT `Condition`) FROM Site_Analytics WHERE (`Condition` <> '' AND `Condition` IS NOT NULL)) AS `TotalUniqueTypesOfErrors`
FROM SiteLog_Master LIMIT 1;

Answer 1

这是我想出来的。我将子查询移动到 FROM 子句，而不是尝试在 SELECT 子句中完成所有操作:

SELECT SUM(t.TotalErrors) FROM (SELECT SUM(IF(`Condition` <> '' AND `Condition` IS NOT NULL,1,0)) AS TotalErrors FROM Site_A nalytics GROUP BY FourID HAVING TotalErrors = 1) t;

子查询返回 45 行，然后 SELECT 子句中的 SUM 将其求和为一行。

为此，我将添加我需要的其余突破。我可能会在 select 子句 SUM 中执行 IF() 并从 FROM 子句中删除 HAVING，这样我就可以创建其他总计:

    SELECT 
    SUM(IF(t.TotalErrors = 1,1,0)) AS Group1, 
    SUM(IF(t.TotalErrors = 2,1,0)) AS Group2, 
    SUM(IF(t.TotalErrors = 3,1,0)) AS Group3, 
    SUM(IF(t.TotalErrors = 4,1,0)) AS Group4, 
    SUM(IF(t.TotalErrors >= 5,1,0)) AS Group5  
    FROM (SELECT SUM(IF(`Condition` <> '' AND `Condition` IS NOT NULL,1,0)) 
AS TotalErrors FROM Site_Analytics GROUP BY FourID) t;

返回:

第 1 组 45
第 2 组 76
第 3 组 90
第 4 组 57
Group5+ 360