CODEWITHSUNDEEP
postgresql
Niloy Rony
Niloy Rony

Reputation: 632

Postgres how can I get previous month sum from relational table group by?

I have two tables

coins       summary_coins 
--------    -------------
id | name    id| date                |  get_count | coin_id
===|====    ==============================================
 1 |lira     1 | 2020-02-16 16:55:50 |  20        | 1
 2 |A        1 | 2020-03-16 16:55:50 |  12        | 1
 3 |B        1 | 2020-03-16 16:55:50 |  20        | 1

My Expected result

name get_count   previous_month_count  
Lira 32          20

By below query I am able to get present month summary sum

SELECT Coins.id AS "Coins__id", 
Coins.name AS "Coins__name",  
(SUM(SummaryCoins.get_count)) AS "get_count", 
(SUM(SummaryCoins.get_count)) AS "previous_month_count" 
FROM coins Coins 
INNER JOIN summary_coins SummaryCoins 
ON Coins.id = (SummaryCoins.coin_id) 
WHERE (SummaryCoins.user_id = 1 AND SummaryCoins.date > '2021-04-01' AND SummaryCoins.date < '2021-05-31') 
GROUP BY Coins.id

How can I get previous month summation ?

Upvotes: 0

Views: 50

Answers (2)

AC at CA
AC at CA

Reputation: 735

CASE WHEN could be a good way to give the solution:

with sum_coins as (
  select coin_id
       , sum(
           case when date_trunc('month', date) = date_trunc('month', CURRENT_DATE)
                then get_count
                else 0 
                end) get_count
       , sum(
           case when date_trunc('month', date) = date_trunc('month', current_date - interval '1' month)
                then get_count
                else 0
                end) previous_month_count
  from summary_coins group by coin_id
)
select coins.name
     , get_count
     , previous_month_count
  from coins
 inner join sum_coins
    on sum_coins.coin_id = coins.id;

And using date_trunc and current_date function can provide an easy way to get exact current month and previous month data.

Upvotes: 0

user330315
user330315

Reputation:

One way to do it, is to extend the date range in the WHERE clause to include both months (or actually all three months), then use filtered aggregation in the SELECT list.

SELECT c.id AS "Coins__id", 
       c.name AS "Coins__name",  
       SUM(sc.get_count) filter (where sc.date >= '2021-03-01' and sc.date < '2021-04-01') AS get_count,
       SUM(sc.get_count) filter (where sc.date >= '2021-04-01' and sc.date < '2021-05-01') AS previous_month_count
FROM coins c
  JOIN summary_coins sc ON c.id = sc.coin_id
WHERE sc.date >= '2021-03-01'  --<< note this includes the "previous" month
  AND sc.date < '2020-05-01'
GROUP BY c.id

When dealing with timestamp values (which you have, despite the misleading column name "date") it's recommended to use one day after the end of the desired range with the < operator so that you are sure that all values on the last day are included.

Upvotes: 1

Related Questions