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.