muhnizar muhnizar - 1 month ago 7
SQL Question

SQL query to calculate total per month as a column

I am stuck on a SQL query. I am using PostgreSQL. I need to get the total for each month for all states.

table A
--------------------------------------------------------
created | Name | Agent_id | Total
--------------------------------------------------------
3/14/2013 | Harun | 1A | 5
3/14/2013 | Hardi | 2A | 20
4/14/2013 | Nizar | 3A | 30
5/14/2013 | moyes | 4A | 20

table B
----------------------------
Agent_id| state_id
----------------------------
1A | 1
2A | 1
3A | 1
4A | 2

table C
----------------------------
state_id | State
----------------------------
1 | Jakarta
2 | Singapore
3 | Kuala lumpur


DESIRED RESULT:

-----------------------------------------------------------------------------------------------
No |State | Januari | February | March | April | Mei ... December| Total
-----------------------------------------------------------------------------------------------
1 |Jakarta |0 |0 |25 | 30 | 0 ... | 55
2 |Singapore |0 |0 | 0 | 0 | 20 ... | 20
3 |Kuala Lumpur |0 |0 | 0 | 0 | 0 ... | 0

Answer

to have all state with no data in table A / B you have to use OUTER JOIN

to complete @bma answer

  select  
  no,
  state,
  sum(case when month = 1 then total else 0 end) as januari,
  sum(case when month = 2 then total else 0 end) as februari,
  sum(case when month = 3 then total else 0 end) as mars,
  sum(case when month = 4 then total else 0 end) as april,
  sum(case when month = 5 then total else 0 end) as may,
  sum(case when month = 6 then total else 0 end) as juni,
  sum(case when month = 7 then total else 0 end) as juli,
  sum(case when month = 8 then total else 0 end) as august,
  sum(case when month = 9 then total else 0 end) as september,
  sum(case when month = 10 then total else 0 end) as october,
  sum(case when month = 11 then total else 0 end) as november,
  sum(case when month = 12 then total else 0 end) as december,
  sum(coalesce(total,0)) as total
from (
    select  
      c.state_id as no,
      extract(month from created) as month,
      state,
      sum(total) as total
    from tablec c 
      left join tableb b on ( b.state_id = c.state_id)
      left join tablea a on ( a.agent_id = b.agent_id)
    group by c.state_id,state,month
    ) sales
    group by no,state;

SQL Fiddle demo