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.

Leave a Comment

Your email address will not be published.

Scroll to Top