CODEWITHSUNDEEP
sqlpostgresql
zaoras
zaoras

Reputation: 71

postgreSQL how many subscriptions were bought each month

I have a table:

Subscriptions (idsub, dstart, dend, #idk)

dstart is date when the subscription was bought (Data type: date)

I need to count how many subscriptions were bought each month this year (or preferably - chosen year). I know It should probably be done with COUNT, however I just started learning SQL and I am struggling with the 'each month' part.

I am using Postgresql

Thanks for help

Upvotes: 1

Views: 282

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270613

I would strongly recommend writing the query like this:

SELECT DATE_TRUNC('month', dstart) as yyyymm COUNT(*) total
FROM Subscriptions
WHERE dstart >= DATE_TRUNC('year', now()) AND
      dstart < DATE_TRUNC('year', now()) + INTERVAL '1 YEAR'
GROUP BY yyyymm
ORDER BY yyyymm;

The important component of this query is the WHERE clause. Although it might look complicated, dstart is not the argument to a function. That helps the optimizer and allows the optimizer to use an index on the column -- if one is available.

Upvotes: 1

Alex V
Alex V

Reputation: 3644

The trick is to apply GROUP BY so your COUNT will go by month. You mention that dstart is the starting date, so we will have to get the month from that. One way to accomplish that is with date_trunc. (The docs for date_trunc can be read here for more info on how it works: https://www.postgresql.org/docs/9.1/functions-datetime.html).

Finally, you can get the current year with CURRENT_DATE and use date_trunc again to only get subscriptions from this year.

All in all, your query should look something like this:

SELECT
    DATE_TRUNC('MONTH', dstart),
    COUNT(idsub)
FROM Subscriptions
; to change the year, just change CURRENT_DATE 
; to a date with the year of your choice
WHERE DATE_TRUNC('YEAR', dstart)=DATE_TRUNC('YEAR', CURRENT_DATE)
GROUP BY DATE_TRUNC('MONTH', dstart)

There are other ways to do this exact thing, but this should solve your basic problem and be more than enough to get you started. From here, for example, you could also sort by number of subscriptions by adding ORDER BY COUNT(idsub) DESC.

Upvotes: 1

danielctf
danielctf

Reputation: 129

Try this

SELECT TO_CHAR(dstart, 'MM/YYYY') date, COUNT(*) total
FROM Subscriptions
WHERE EXTRACT(year from dstart) = 2019 
GROUP BY TO_CHAR(dstart, 'MM/YYYY')

Upvotes: 2

Related Questions