CODEWITHSUNDEEP
sql
Sharath
Sharath

Reputation: 2428

Get total users for each month doing a group by on month and user_id in mysql

+----------------------------------+
|id |user_id | created_at | status
+----------------------------------+
| 5 | 10    | 2019-08-01 | PAID |
| 6 | 10    | 2019-09-01 | PAID |
| 7 | 10    | 2019-09-01 | PAID |
| 8 | 11    | 2019-08-01 | PAID |
| 9 | 11    | 2019-08-01 | PAID |
|10 | 13    | 2019-09-01 | PAID |
|11 | 15    | 2019-08-01 | PAID |
|12 | 16    | 2019-08-01 | PAID |
|17 | 25    | 2019-09-01 | PAID |
|18 | 25    | 2019-08-01 | PAID |
|28 | 37    | 2019-08-01 | PAID |
|29 | 38    | 2019-09-01 | PAID |
|30 | 37    | 2019-10-01 | PAID |
|31 | 38    | 2019-10-01 | PAID |
+--------------------------------+

How to fetch total users for each month doing a group by on created_at and user_id. Ideally if there are user_id's repeating in a month then it should be considered as single user.

So basically the output should be like shown below where its showing unique users in each month

count   created_at
---------------------
6       2019-08-01
4       2019-09-01
2       2019-10-01

Tried with below query but not able to apply group by on user_id gives wrong results.

SELECT date_trunc('month', created_at) as datetime, count(*) FROM payments
where status = 'PAID'
and created_at >= '2019-08-01 00:00:00' and created_at <= '2019-10-30 00:00:00'
GROUP BY date_trunc('month', created_at) order by datetime;

Upvotes: 1

Views: 788

Answers (2)

Fahmi
Fahmi

Reputation: 37473

You can try using count(distinct userid)-

SELECT date_trunc('month', created_at) as datetime, count(distinct userid) FROM payments
where status = 'PAID'
and created_at >= '2019-08-01 00:00:00' and created_at <= '2019-10-30 00:00:00'
GROUP BY date_trunc('month', created_at) order by datetime

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

To count unique user_id per month, just use COUNT(DISTINCT ..):

SELECT date_trunc('month', created_at) as datetime, 
       count(distinct user_id) as user_count
FROM payments
WHERE status = 'PAID'
      and created_at >= '2019-08-01 00:00:00' 
      and created_at <= '2019-10-30 00:00:00'
GROUP BY date_trunc('month', created_at) 
ORDER BY datetime

Upvotes: 1

Related Questions