Sergey Ivanov 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?

Answer

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")

Comments