0

I have the following query to get the monthly amount of users:

SELECT count(user_id) from subs
where (started_at between @start_date and @start_date + interval 1 month
or (expires_at>@start_date + interval 1 month and started_at<@start_date))

If we had the following DB:

user_id started_at expires_at

=============================
1 2015-01-01 2015-12-31
2 2015-01-01 2015-01-03
3 2015-02-01 2015-02-28
4 2015-03-01 2015-03-31
5 2015-04-01 2015-04-31
6 2015-04-01 2016-04-01
7 2015-05-01 2015-05-09

I need a query that will return the following table:

2015-01 - 2
2015-02 - 2 (because one of Jan records doesn't expire till Dec)
2015-03 - 2
2015-04 - 3
2015-05 - 3

etc

So what is the efficient way to get this result in one query?

samer fatayri
  • 1
  • 1

2 Answers2

0

You want to GROUP BY the year and month.

Assuming your started_at column is of a DATE type, you can f.e. use GROUP_BY YEAR(started_at), MONTH(started_at), or also use DATE_FORMAT to format the column value to a single string value, of the form YYYY-MM and GROUP BY that. Select that same value as a column too, to get the proper identifier you want.

CBroe
  • 91,630
  • 14
  • 92
  • 150
  • Seems you didn't get me: I want to run the query (started_at between start_date and start_date + interval 1 month or (expires_at>start_date + interval 1 month and started_at – samer fatayri Apr 02 '16 at 02:33
  • Well if multiple people here don't seem to get you, then that might rather be a problem with your explanation so far. Please go edit your question, and include table structures, example data, and the expected outcome. – CBroe Apr 02 '16 at 13:35
0

You probably want something like this:

SELECT YEAR(started_at) as 'Year',
MONTH(started_at) as 'Month',
COUNT(user_id) as 'Users'
FROM subs
GROUP BY YEAR(started_at),MONTH(started_at);

Note that in case a month has no users, this query will not return an entry for that month. If you want to also include months with 0 users you want a more complex query; check this for more info.

Community
  • 1
  • 1
Sevle
  • 3,109
  • 2
  • 19
  • 31
  • Seems you didn't get me: I want to run the query (started_at between start_date and start_date + interval 1 month or (expires_at>start_date + interval 1 month and started_at – samer fatayri Apr 02 '16 at 02:32
  • I'm with CBroe on this one. You should add the information that he mentioned in order for us to understand what you want exactly. If you can't share data from your database, give as a dummy sample and expected outcome. – Sevle Apr 03 '16 at 17:29