Looking at your query, I doubt that you actually need a join
in the subquery. Doesn’t this do what you want?
SELECT r.coupon_id, c.who_added, c.coupon_name, r.when_added,
(
SELECT COUNT(*)
FROM redeemed_coupons r2
WHERE r2.coupon_id = c.coupon_id AND r2.when_added = r.when_added
) cnt
FROM redeemed_coupons r
INNER JOIN coupon c ON c.coupon_id = r.coupon_id
WHERE r.when_added BETWEEN 1602827745 AND 1613084678 AND c.who_added = 1
ORDER BY r.when_added ;
Apart from the changes in the suqbuery, I also re-organized a little the conditions in the outer query; this does not change the result, but I find it more readable to only put the correlation conditions in the ON
clause, and other conditions in the WHERE
clause.
You could also express this with a window count()
, if you are running MySQL 8.0:
SELECT r.coupon_id, c.who_added, c.coupon_name, r.when_added,
COUNT(*) OVER(PARTITION BY r.coupon_id, r.when_added) cnt
FROM redeemed_coupons r
INNER JOIN coupon c
ON r.coupon_id = c.coupon_id
AND c.who_added = 1
AND r.when_added BETWEEN 1602827745 AND 1613084678
ORDER BY r.when_added ;
CLICK HERE to find out more related problems solutions.