muhnizar - 11 days ago 4x
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
``````

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

``````  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