CODEWITHSUNDEEP
mysqlsql
haneulkim
haneulkim

Reputation: 4928

count of users who've bought same item on a given day 1,2,3,...n in MySql

I using MySql version 5.7

I have a table that looks like this:

user_id  item_id      date
   1        2       2020-01-01
   1        2       2020-01-01
   1        2       2020-01-01
   1        3       2020-01-01
   1        4       2020-01-01
   33       7       2020-02-02
   33       7       2020-02-02
   44       11      2020-02-02
   44       11      2020-02-02

I want to count number of users who have bought same item on a given day.

Desired result:

  date        one   two    three
2020-01-01     1     0       1
2020-02-02     0     2       0

one column = number of users who've bought same item once in a given day
two column = number of users who've bought same item twice in a given day.

Let me know if anything is unclear.

Thanks in advance!

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Hmmm . . . Two levels of aggregation:

select date,
       sum(cnt = 1) as one,
       sum(cnt = 2) as two,
       sum(cnt = 3) as three
from (select date, user_id, item_id, count(*) as cnt
      from t
      group by uesr_id, item_id
     ) ui
group by date

Upvotes: 1

Related Questions