Count how many rows have the same value but if the ID are different count it separate

Do you just want a count using a window function?

select r.coupon_id, c.who_added, c.coupon_name, r.when_added,
       count(*) over (partition by r.coupon_id, r.when_added) as cnt
from redeemed_coupons r 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 ;

EDIT:

In older versions of MySQL, you can use a correlated subquery:

select r.coupon_id, c.who_added, c.coupon_name, r.when_added,
       (select count(*) 
        from redeemed_coupons r2 join
             coupon c2
             on r2.coupon_id = c2.coupon_id and
                c2.who_added = 1 and
                r2.when_added BETWEEN 1602827745 AND 1613084678
        where r2.when_added = r.when_added
      )
from redeemed_coupons r 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 ;

Note that this is much less efficient than using window functions.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top