Need to SUM All Rows by a Non-Numeric Condition AND a GROUP BY

Question

I have a table that contains a condition that I need to test for and total it by a group then get the total of the number of groups that equal a value.

Example:

Group 1 total of conditions = 1
Group 2 total of conditions = 14
Group 3 total of conditions = 5
Group 4 total of conditions = 1

etc.

I need to calculate the total of each group then get the total groups that = 1

This is the base code that I have come up with to do the basic group totals. (I'm am using a SUM(IF(condition = true,1,0)) in an effort to build a query that can then be used as a subquery):

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

of course this is the same as:

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

Returns a row for each GROUP with the group total. I then need to GROUP the GROUPS by GROUP TOTAL.

Either way I then need to get the number of GROUPS (FourID) that = 1 (or 2,3,4 etc)

The end result I'm looking for is to generate a report that has number of groups that = 1, 2,3,4,5,5+

I have tried using the above queries as sub-queries but with no success such as:

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

This is a query I used for getting other totals for a similar report:

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;

Sounds like you'll need to use a `HAVING` clause. Check out this related (?) question: http://stackoverflow.com/a/9253267/108147 — Mathew, May 29 '14 at 17:15
I did try this but it returns a row for each group that = 1 but I need to total the number of groups that = 1: {SELECT SUM(IF(`CONDITION` <> '' AND `Condition` IS NOT NULL,1,0)) AS TotalErrors FROM Site_Analytics GROUP BY FourID HAVING TotalErrors = 1;} — g-man, May 29 '14 at 17:23
I then tried to use it as a sub-query that I could then SUM but that throws an error {SELECT SUM(SELECT SUM(IF(CONDITION <> '' AND Condition IS NOT NULL,1,0)) AS TotalErrors FROM Site_Analytics GROUP BY FourID HAVING TotalErrors = 1) FROM Site_Analytics} — g-man, May 29 '14 at 17:32

g-man · Answer 1 · 2014-05-29T18:25:54.947

This is what I came up with. I moved the sub-query to the FROM clause instead of trying to do it all in the SELECT clause:

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;

The sub-query returns 45 rows then the SUM in the SELECT clause sums it into a single row.

To this I will add the remaining breakouts that I need. I will likely do an IF() in the select clause SUM and remove the HAVING from the FROM clause so I can create other summed totals:

    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;

Returns:

Group1 45
Group2 76
Group3 90
Group4 57
Group5+ 360

Need to SUM All Rows by a Non-Numeric Condition AND a GROUP BY

1 Answers1