Sergey Ivanov - 11 months ago 55

SQL Question

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 Source

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