Sergey Ivanov - 3 months ago 13
SQL Question

# Aggregate data by months in Vertica

I have a Vertica table like this:

``````date number
'2016-01-01' 47
'2016-01-02' 32
'2016-04-13' 54
``````

I want to summarize it like this:

``````Jan Feb Mar Apr ...
150 102 30 0
``````

Where second row is the sum of all numbers for that month.

How can I do this?

You'll need to create a sum on every column with a case statement to only sum on the correct value.

``````SELECT SUM(CASE WHEN MONTH("DATE") = 1 THEN "NUMBER" END) Jan,
SUM(CASE WHEN MONTH("DATE") = 2 THEN "NUMBER" END) Feb,
SUM(CASE WHEN MONTH("DATE") = 3 THEN "NUMBER" END) Mar,
SUM(CASE WHEN MONTH("DATE") = 4 THEN "NUMBER" END) Apr,
SUM(CASE WHEN MONTH("DATE") = 5 THEN "NUMBER" END) May,
SUM(CASE WHEN MONTH("DATE") = 6 THEN "NUMBER" END) Jun,
SUM(CASE WHEN MONTH("DATE") = 7 THEN "NUMBER" END) Jul,
SUM(CASE WHEN MONTH("DATE") = 8 THEN "NUMBER" END) Aug,
SUM(CASE WHEN MONTH("DATE") = 9 THEN "NUMBER" END) Sep,
SUM(CASE WHEN MONTH("DATE") = 10 THEN "NUMBER" END) Oct,
SUM(CASE WHEN MONTH("DATE") = 11 THEN "NUMBER" END) Nov,
SUM(CASE WHEN MONTH("DATE") = 12 THEN "NUMBER" END) Dec
FROM mytable
``````

You also could possible add another field to do this per year and then group / order on it (if that's what you want to do). `YEAR("DATE")`

Source (Stackoverflow)