CODEWITHSUNDEEP
sqlamazon-redshift
sam
sam

Reputation: 873

Redshift - How can I coalesce and add along the row dimension?

How can I coalesce and maybe add vertically? Say I have a table such as:

| NAME   | Metric1 | Metric2 | Metric3 | Metric4 |
|--------|---------|---------|---------|---------|
| class1 | NULL    | 3       | NULL    | 4       |
| class1 | 1       | NULL    | 4       | NULL    |
| class2 | 5       | 2       | 6       | NULL    |
| class2 | NULL    | NULL    | 1       | NULL    |
| class2 | 4       | NULL    | NULL    | NULL    |

with 2 classes(class and class2. Sometimes a metric will be NULL, and sometimes it will be populated. I would like an end result like so:

| NAME   | Metric1 | Metric3 | Metric3 | Metric4   |
|--------|---------|---------|---------|-----------|
| class1 | 1       | 3       | 4       | 4         |
| class2 | 9       | 2       | 7       | NULL or 0 |

where the results are grouped by class name, and the metrics are "coalesced" if only 1 instance is populated or added if multiple are. Any hints?

Upvotes: 0

Views: 239

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Just use sum():

select name, sum(metric1), sum(metric2), sum(metric3), sum(metric4)
from t
group by name;

Upvotes: 3

Related Questions