CODEWITHSUNDEEP
sqlamazon-redshift
Ajinkya Vijay Koshti
Ajinkya Vijay Koshti

Reputation: 11

Redshift SQL- How to groupby all columns but not create a separate row for each date. Select latest date instead

I have a table like this

+---------+-------+----------+------------+-------+
| Product | Grade | Location | Date       | Value |
+---------+-------+----------+------------+-------+
| A       | I     | GLOBAL   | 2020-02-11 | 100   |
+---------+-------+----------+------------+-------+
| A       | I     | GLOBAL   | 2020-03-22 | 200   |
+---------+-------+----------+------------+-------+
| B       | II    | ASIA     | 2020-02-11 | 300   |
+---------+-------+----------+------------+-------+
| B       | II    | ASIA     | 2020-04-22 | 300   |
+---------+-------+----------+------------+-------+
| C       | I     | GLOBAL   | 2020-04-04 | 100   |
+---------+-------+----------+------------+-------+

I want to use groupby on all but select latest date instead of creating new row for each date.

Desired result:

+---------+-------+----------+------------+-------+
| Product | Grade | Location | Date       | Value |
+---------+-------+----------+------------+-------+
| A       | I     | GLOBAL   | 2020-03-22 | 300   |
+---------+-------+----------+------------+-------+
| B       | II    | ASIA     | 2020-04-22 | 600   |
+---------+-------+----------+------------+-------+
| C       | I     | GLOBAL   | 2020-04-04 | 100   |
+---------+-------+----------+------------+-------+

Upvotes: 0

Views: 1668

Answers (1)

Fahmi
Fahmi

Reputation: 37473

Use aggregation with group by

select product, grade, location, max(date) as date,sum(value) as value
from tablename
group by product, grade, location

Upvotes: 1

Related Questions