postgresql - Sum(Case when) resulting in multiple rows of the selection -

i have huge table of customer orders , want run 1 query list orders month past 13 months 'user_id'. have (below) works instead of listing 1 row per user_id lists 1 row each order user_id has. ex: 1 user has 42 total orders on life lists user_id in 42 rows , each row has 1 payment. typically throw in pivot table in excel i'm on million row limit need right , have had 0 success. read out this:

user_id | jul_12 | aug_12 |

123456 | 150.00 | 150.00 |

not this:

user_id | jul_12 | aug_12 |

123456 | 0.00 | 150.00 |

123456 | 150.00 | 0.00 |

etc. 40 more rows

select ui.user_id,  sum(case when date_part('year', o.time_stamp) = 2012 , date_part('month', o.time_stamp) = 07 o.amount else 0 end) jul_12, sum(case when date_part('year', o.time_stamp) = 2012 , date_part('month', o.time_stamp) = 08 o.amount else 0 end) aug_12, orders o join users_info ui on ui.user_id = o.user_id user_id = '123456' group ui.user_id, o.time_stamp; 

try like:

select ui.user_id,  sum(case when date_part('year', o.time_stamp) = 2012 , date_part('month', o.time_stamp) = 07 o.amount else 0 end) jul_12, sum(case when date_part('year', o.time_stamp) = 2012 , date_part('month', o.time_stamp) = 08 o.amount else 0 end) aug_12, orders o join users_info ui on ui.user_id = o.user_id user_id = '123456' group ui.user_id; 

you getting 1 row per order because grouping o.time_stamp , timestamps different each order.

a shorter version of query:

select ui.user_id,  sum(case when date_trunc('month', o.time_stamp) = to_date('2012 07','yyyy mm') o.amount end) jul_12, sum(case when date_trunc('month', o.time_stamp) = to_date('2012 08','yyyy mm') o.amount end) aug_12, orders o  join users_info ui on ui.user_id = o.user_id ui.user_id = '123456' group ui.user_id;