CODEWITHSUNDEEP
postgresql
myohmy
myohmy

Reputation: 39

How to calculate monthly total from a given date

I have the following table

CREATE TABLE tickets(
  Id INT, 
  date_ date
);

and the following data

INSERT INTO tickets (id, date_)
VALUES (1, '2019-04-01'), 
    (2, '2019-04-02'), 
    (3, '2019-05-01'), 
    (4, '2019-05-02’);

Each row represents a ticket bought. I’m trying to calculate the monthly number of tickets purchased from a given date.

For example,

2019-04-01 should return 1 because only 1 ticket was purchased from 2019-03-01 through 2019-04-01

2019-01-02 should return 2 because 2 tickets were purchased between 2019-03-02 through 2019-04-02

2019-05-01 3

2019-05-02 3

I tried the following

SELECT count(id) as count_tickets,
    date_
    FROM tickets
    WHERE date_ <= date_
    AND date_ >= date_ - interval '1' month
    GROUP BY date_

But that only returns the total for that day. How do I get the total monthly from a given date? Monthly meaning 1 month prior to a given date through the given date.

Upvotes: 0

Views: 26

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Use a correlated query:

SELECT t1.date_, (SELECT COUNT(*) 
                  FROM tickets t2 
                  WHERE t2.date_ <= t1.date_ 
                    AND t2.date_ >= t1.date_ - interval '1' month) as total_tickets
FROM tickets t1

if date_ is a parameter then just remove the group by:

SELECT count(id) as count_tickets
FROM tickets
WHERE date_ <= the_date
  AND date_ >= the_date - interval '1' month

Upvotes: 0

Related Questions