# 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;