0

This might be a simple one but I haven't got a solution yet. I have a create_date field which is a date type, and a revenue number. I want to see weekly break down of revenue.

I can get the numbers easily in tableau because of built in functionality but doing it in PostgreSQL is where I need some help.

Nemo
  • 1,111
  • 6
  • 28
  • 45
  • Like in `SELECT EXTRACT(week FROM datecolumn)`?. You can also filter on [`EXTRACT()`](http://www.postgresql.org/docs/9.4/static/functions-datetime.html) but mind your indexes. – dhke Apr 22 '16 at 18:01
  • Tried that but I m getting syntax error. I did SELECT EXTRACT(WEEK FROM TIMESTAMP bf.create_date) from my_table. Am I wrong in syntax? – Nemo Apr 22 '16 at 18:47
  • The `TIMESTAMP` part is required for timestamp literals. Leave it out when extracting from a table column. – dhke Apr 22 '16 at 19:01
  • We can help better if you add your existing postgres code. – FirstName LastName Apr 22 '16 at 21:33
  • Thanks Alastor, dhke for your replies. I got it working the way dhke suggested. It was just a syntax issue. – Nemo Apr 27 '16 at 15:08

1 Answers1

0

If you want the revenue by week, you'll need to group and aggregate:

select extract (week from create_date) as week, sum(revenue) from table group by week

Max Gasner
  • 1,191
  • 1
  • 12
  • 18