CODEWITHSUNDEEP
sqlpostgresql
mext
mext

Reputation: 31

Sum total Sales and Buys at same record group by month?

I have a table which is like this.

id date       subtotal type
1 |2017-12-12 | 50.00 | 1
2 |2017-12-12 | 20.00 | 2
3 |2017-11-12 | 30.00 | 2
4 |2017-11-12 | 40.00 | 1
5 |2017-10-12 | 70.00 | 1
6 |2017-10-12 | 250.00| 2

In that case the type column presents sales (1) and buys(2). What I want to do is, group by this orders by month and get the total sale and buys at this month. Something like this.

id date        sale     buy
1 |December   | 50.00 | 20.00
2 |November   | 30.00 | 40.00
3 |October    | 70.00 | 250.00

When I try something like this,

 select to_char(date,'Mon') as Month,
       extract(year from date) as Year,
       case when type= 1 then sum("subtotal")  END as sales,
       case when type= 2 then sum("subtotal")  END as buys
   from table
   group by 1,2,type

result does not look like what I want. The months will appear at different columns. Like this.

month year    sales buys
Oct   |2017|  70.00 | 0
Oct   |2017|  0     | 250.00

How can I accomplish this? I just want to monthly sum records.

Upvotes: 0

Views: 452

Answers (3)

nazmul.3026
nazmul.3026

Reputation: 1008

You can Try this

Select a.Month,a.Year,sum(a.sales) sales,sum(a.buys) buys 
from ( 
  select convert(char(3),date, 0) as Month,
         year(date) as Year,
         isnull(case when type= 1 then sum(subtotal)  END,0) as sales,
         isnull(case when type= 2 then sum(subtotal)  END,0) as buys    
   from _table    
   group by convert(char(3),date, 0),year(date),type
 ) a   
 group by a.Month,a.Year

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270523

You want conditional aggregation:

select to_char(date,'Mon') as Month,
       extract(year from date) as Year,
       sum(case when type = 1 then subtotal else 0 end) as sales,
       sum(case when type = 2 then subtotal else 0 end) as buys
from table
group by Month, Year;

I often find it convenient to use date_trunc() in this situation:

select date_trunc('month', date) as month_start,
       sum(case when type = 1 then subtotal else 0 end) as sales,
       sum(case when type = 2 then subtotal else 0 end) as buys
from table
group by month_start
order by month_start;

Upvotes: 1

Niranjan Rajawat
Niranjan Rajawat

Reputation: 563

In your query, you are grouping by year also, that's why its totaling everything in same row. This should give you what you want:

select to_char(date,'Mon') as Month,
       extract(year from date) as Year,
       case when type= 1 then sum("subtotal")  END as sales,
       case when type= 2 then sum("subtotal")  END as buys
   from table
   group by 1

Upvotes: 0

Related Questions