我有一个表,其中包含一个条件,需要对其进行测试,并由一个组对其进行汇总,然后得到等于某个值的组数的总和。
示例:
等。
我需要计算每个组的总数,然后得到=1的总组
这是我想出的基本代码,用于完成基本的组总数。(我正在使用SUM(IF(条件= 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;返回包含组总数的每个组的一行。然后,我需要按组的总数分组。
无论哪种方式,我都需要得到=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;发布于 2014-05-29 18:14:17
这就是我想出来的。我将子查询移到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子句中删除other,这样我就可以创建其他的总和:
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;返回:
Group1 45
Group2 76
Group3 90
Group4 57
Group5+ 360
https://stackoverflow.com/questions/23938800
复制相似问题