Mysql – How to get a count for an aggregated row

aggregateMySQL

I am getting sums for a set of rows, There is an id from each row that relates to 0 to many rows in another table. I am having trouble getting that number into the dataset. So here is what I have which works as I need it to:

SELECT
  sp.advcode         AS Advertiser,
  count(*)           AS Created,
  sum(CASE WHEN i.sent IS NOT NULL THEN 1 ELSE 0 END)    AS Sent,
  sum(CASE WHEN ((i.active = 1) AND (i.sent IS NULL)) THEN 1 ELSE 0 END) AS ActiveUnsent,
  count(i.inquiryId) AS Total,
  sum(CASE WHEN i.campaignLeadId IS NOT NULL THEN 1 ELSE 0 END) AS CampaignLeads,
  sum(CASE WHEN i.active = 0 THEN 1 ELSE 0 END) AS Inactive
FROM inquiry i
LEFT JOIN schoolProfile sp ON sp.schoolProfileId = i.schoolProfileId
WHERE sp.advcode LIKE 'al007'
  AND i.dateCreated BETWEEN '2016-02-02' AND '2016-03-02'
GROUP BY i.schoolProfileId;

I need to count how many times i.inquiryId appears in another table, per returned row. If someone has a clue I would be most grateful.

Best Answer

SELECT  sp.advcode AS Advertiser,
        ii.*
    FROM  
      ( SELECT  count(*) AS Created,
                COUNT(i.sent) AS Sent,
                sum((i.active = 1) AND (i.sent IS NULL)) AS ActiveUnsent,
                count(i.inquiryId) AS Total,
                COUNT(i.campaignLeadId) AS CampaignLeads,
                sum(i.active = 0) AS Inactive
            FROM  inquiry i
            WHERE  i.dateCreated >= '2016-02-02'
              AND  i.dateCreated  < '2016-02-02' + INTERVAL 1 MONTH
            GROUP BY  i.schoolProfileId 
      ) AS ii
    LEFT JOIN  schoolProfile sp ON sp.schoolProfileId = ii.schoolProfileId ; 
    WHERE  sp.advcode LIKE 'al007'

Notes:

  • By not having a JOIN, the counts are more likely to be correct.
  • COUNT(nullable-column) simply count how many non-nulls there are
  • Boolean expressions evaluate in SUM() as 0 or 1, thereby obviating the CASE.
  • In your BETWEEN, you probably included an extra day.
  • i needs INDEX(dateCreated)
  • I assume you are using InnoDB and schoopProfileId is the PRIMARY KEY of schoolProfile?
  • If you really need LEFT, then you might get NULL for Advertiser?
  • If you don't need LEFT, then ...

The following might be faster:

SELECT  
      ( SELECT  advcode
            FROM  schoolProfile
            WHERE  schoolProfileId = i.schoolProfileId 
              AND  advcode LIKE 'al007'
      ) AS advertiser,
        count(*) AS Created,
        COUNT(i.sent) AS Sent,
        sum((i.active = 1) AND (i.sent IS NULL)) AS ActiveUnsent,
        count(i.inquiryId) AS Total,
        COUNT(i.campaignLeadId) AS CampaignLeads,
        sum(i.active = 0) AS Inactive
    FROM  inquiry i
    WHERE  i.dateCreated >= '2016-02-02'
      AND  i.dateCreated  < '2016-02-02' + INTERVAL 1 MONTH
      AND  EXISTS 
      ( SELECT  *
            FROM  schoolProfile
            WHERE  schoolProfileId = i.schoolProfileId 
              AND  advcode LIKE 'al007'
      )
    GROUP BY  i.schoolProfileId;
Related Question