Q:

Select count date from multiple where conditions for each day

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 |
+-------------------+------------+-------------+-----------+

A:

Not knowing what your data structure looks like, I can only speculate on what the solution should be.  However, my guess is that a query such as the following is what you want:
SELECT COUNT(DISTINCT cv.id) as number_of_coupons,
       DATE_FORMAT(cv.assigned_date, '%d-%m-%Y') as date,
       SUM(CASE WHEN c.campaign_id = 1 THEN 1 ELSE 0 END) as campaign_1,
       SUM(CASE WHEN c.campaign_id = 2 THEN 1 ELSE 0 END) as campaign_2
FROM coupon_vault cv LEFT JOIN
     coupons c
     ON cv.coupon_id = c.coupon_id
GROUP BY DATE_FORMAT(cv.assigned_date, '%d-%m-%Y')
ORDER BY MIN(cv.assigned_date);

It is quite possible that the COUNT(DISTINCT) is unnecessary and COUNT() would suffice.
Both Postgres and MySQL (your original tag) have reasonable alternatives for the SUM(CASE . . .).  However, you have not specified your database, so I'm sticking with the code that works in both databases.

