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