0

I have lot of coupons. I would like to learn for each day how many coupons from each campaign have been received by users. But I cannot write something like assigned_date for each select row

SELECT count(id)                                                  as number_of_coupons,
       DATE_FORMAT(assigned_date, '%d-%m-%Y')                     as date,
       (SELECT COUNT(id) FROM coupon WHERE campaign_id = 1 AND assigned_date=THIS MUST BE SOMETHING) as campaign_1,
       (SELECT COUNT(id) FROM coupon WHERE campaign_id = 2 AND assigned_date=THIS MUST BE SOMETHING) as campaign_2
FROM coupon
GROUP BY DATE_FORMAT(assigned_date, '%d-%m-%Y')
order by STR_TO_DATE(date, '%d-%m-%Y') DESC

So the result will be something like. How can I achieve this result?

+-------------------+------------+-------------+-----------+
| number of coupons |    date    |  campaign_1 | campaign2 |
+-------------------+------------+-------------+-----------+
|               156 | 12-10-2019 |        6980 |       100 |
|               177 | 11-10-2019 |        6980 |       100 |
|                44 | 10-10-2019 |        6980 |       100 |
|                94 | 09-10-2019 |        6980 |       100 |
|                93 | 08-10-2019 |        6980 |       100 |
+-------------------+------------+-------------+-----------+
0
SELECT count(id)                                       as number_of_coupons,
       DATE_FORMAT(assigned_date, '%d-%m-%Y')          as date,
       SUM(CASE WHEN campaign_id=1 THEN 1 ELSE 0 END)  as campaign_1,
       SUM(CASE WHEN campaign_id=2 THEN 1 ELSE 0 END)  as campaign_2
FROM coupon
GROUP BY DATE_FORMAT(assigned_date, '%d-%m-%Y')
order by STR_TO_DATE(date, '%d-%m-%Y') DESC
  • Why SUM works fine but COUNT gives the same campaign_1 campaign_2 count? – SNaRe Oct 12 at 15:32
  • You have to ADD all the ones and zeros together. check output of select assigned_date, CASE WHEN campaign_id=1 THEN 1 ELSE 0 END as campaign_1, CASE WHEN campaign_id=2 THEN 1 ELSE 0 END as campaign_2 FROM coupon – Luuk Oct 12 at 15:34
  • 1
    You are doing a sub-select over another "copy" of the coupon table, and that copy is not correlated to the main one. With Luuk's version there is no sub-select going on. – jjanes Oct 12 at 16:21

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.