CODEWITHSUNDEEP
mysql
Sagar
Sagar

Reputation: 269

Incremental counts in mysql

I have a table "UserLogins" and when the user login into the system I will drop a record in the "UserLogins" table.

Jan 10th (Users : 1,2,3,4)   // four records with Ids 1,2,3,4
Jan 20th (1,2,3,4,5,6)       // six records with Ids 1,2,3,4,5,6
Jan 30th (1,2)

Feb 10th (1,7)
Feb 20th (2,6,8)
Feb 25th (1,2,3,5)

Mar 10th (3,4)
Mar 20th (4,5,9)
Mar 30th (8,10,11)

Apr 10th (10,12)
Apr 20th (1,2,3,6,13, 14, 15)
Apr 30th (11,12,16)

When I write the group by my results as follows

Jan - 6
Feb - 7
Mar - 7
Apr - 11

But I need an out put like as follows

Upto Jan - 6 //count of distinct users upto Jan
Upto Feb - 8 //count of distinct users upto Feb
Upto Mar - 11 //count of distinct users upto Mar
Upto Apr - 16 //count of distinct users upto Apr

Upvotes: 0

Views: 42

Answers (1)

fthiella
fthiella

Reputation: 49049

Your first count could simply be like this:

SELECT
  DATE_FORMAT(login_date, '%Y-%b') AS year_month,
  COUNT(DISTINCT user_id)
FROM
  UserLogins
GROUP BY
  DATE_FORMAT(login_date, '%Y-%b')

while to count all users up to a given mount, I would use a Join:

SELECT
  DATE_FORMAT(last_day, '%Y-%b') AS year_month,
  COUNT(DISTINCT user_id)
FROM
  (SELECT DISTINCT LAST_DAY(login_date) as last_day
   FROM UserLogins) d
  INNER JOIN
  UserLogins ON UserLogins.login_date<=last_day
GROUP BY
  DATE_FORMAT(last_day, '%Y-%b')

on the subquery d I will return all last days of every month that has a record on the UserLogins table, then I will join all userlogin that logged up to the end of the month, and then I will do the count.

Upvotes: 3

Related Questions