0

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.

  • 1
    In your original query, you'll have multiple inquiryIds per row. You want to take that group of inquiries and find out how many of those appear in the other table (per schoolProfile)? – paulbarbin Mar 2 '16 at 18:13
  • It is a bit tricky to understand what your query does, or is supposed to do. You select sp.advcode and group by i.schoolProfileId. Can you provide some sample data in an sqlfiddle? – Lennart Mar 2 '16 at 21:08
  • Insert the aggregated record set into a temp table and then run a count against that. – CoveGeek Mar 4 '16 at 16:51
1
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.