9

I have a huge table of customer orders and I want to run one query to list orders by month for the past 13 months by 'user_id'. What I have now (below) works but instead of only listing one row per user_id it lists one row for each order the user_id has. Ex: one user has 42 total orders over his life with us so it lists his user_id in 42 rows and each row only has one payment. Typically I would just throw this in a pivot table in excel but I'm over the million row limit so I need for it to be right and have had zero success. I would like for the read out to look like 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 AND date_part('month', o.time_stamp) = 07 THEN o.amount ELSE 0 END) jul_12,
SUM(CASE WHEN date_part('year', o.time_stamp) = 2012 AND date_part('month', o.time_stamp) = 08 THEN o.amount ELSE 0 END) aug_12,
FROM orders o JOIN users_info ui ON ui.user_id = o.user_id
WHERE user_id = '123456'
GROUP BY ui.user_id, o.time_stamp;
greenpowerade
  • 93
  • 1
  • 1
  • 3

1 Answers1

18

Try something like:

SELECT ui.user_id, 
SUM(CASE WHEN date_part('year', o.time_stamp) = 2012 AND date_part('month', o.time_stamp) = 07 THEN o.amount ELSE 0 END) jul_12,
SUM(CASE WHEN date_part('year', o.time_stamp) = 2012 AND date_part('month', o.time_stamp) = 08 THEN o.amount ELSE 0 END) aug_12,
FROM orders o JOIN users_info ui ON ui.user_id = o.user_id
WHERE user_id = '123456'
GROUP BY ui.user_id;

You were getting one row per order because you were grouping by o.time_stamp and timestamps are different for 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') THEN o.amount END) jul_12,
SUM(CASE WHEN date_trunc('month', o.time_stamp) = to_date('2012 08','YYYY MM') THEN o.amount END) aug_12,
FROM orders o 
JOIN users_info ui ON ui.user_id = o.user_id
WHERE ui.user_id = '123456'
GROUP BY ui.user_id;
Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
  • What if I wanted to cross over years? So if those two instead of 07 July and 08 August where 2012 12 December and 2013 01 January. My goal for this is to run it over a long period of time to see order trends. Thanks! – greenpowerade Aug 07 '13 at 19:34
  • You can still use the `CASE` you were using - I moved the year filter to `WHERE` to shorten the query and speed it up. – Ihor Romanchenko Aug 07 '13 at 19:37
  • Duh - that's a bad on me I guess. Thanks! Is there a different way to approach the multiple columns? Or if I want to pull 24 months am I going to have 24 lines, one for each month? – greenpowerade Aug 08 '13 at 11:51
  • @greenpowerade You can try [`this`](http://www.postgresql.org/docs/current/static/tablefunc.html). But the best solution would be get the data in a simple `id, month, sum` table and pivot it on client side with some programming language. – Ihor Romanchenko Aug 08 '13 at 12:16