CODEWITHSUNDEEP
mysqlsqlcountgroup-by
mohsen Lzd
mohsen Lzd

Reputation: 313

Count the number of occurness before certain date

In MySQL I have a table of records with these columns: id,date,voucher_id,person_id

example db

id         Date         voucher_id   person_id
-----      ------       ----------   ----------
0          2016-02-17   null         1
1          2016-02-18   null         1
2          2016-02-19   1            1
3          2016-02-20   2            2
4          2016-02-21   null         2
5          2016-02-22   3            2
6          2016-02-23   null         1
7          2016-02-24   4            1
8          2016-02-24   null         3
9          2016-02-25   null         3

And I want to know the list of persons that uses vouchers and count how many times that person have record in my table before the usage day of that voucher.

The result for about table would be:

 Date         voucher_id   person_id    count_till_now
 ------       -------      ----------   --------------
 2016-02-19   1            1            2
 2016-02-20   2            2            0
 2016-02-22   3            2            1
 2016-02-24   4            1            4

Which is the best query for generating this result?

Upvotes: 1

Views: 2607

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

One way of doing it is with a correlated subquery:

SELECT `Date`, voucher_id, person_id,
       (SELECT COUNT(*) 
        FROM mytable AS t2
        WHERE t2.person_id = t1.person_id AND
              t2.`Date` < t1.`Date`) AS count_till_now
FROM mytable AS t1
WHERE t1.voucher_id IS NOT NULL

Demo here

Alternatively you can use LEFT JOIN:

SELECT t1.`Date`, t1.voucher_id, t1.person_id, 
       COUNT(t2.person_id) AS count_till_now
FROM mytable AS t1
LEFT JOIN mytable AS t2 ON t1.person_id = t2.person_id AND t1.`Date` > t2.`Date`
WHERE t1.voucher_id IS NOT NULL
GROUP BY t1.`Date`, t1.voucher_id, t1.person_id

Demo here

Upvotes: 3

Related Questions