i have the following table:
date | value | Group |
---|---|---|
2021-04-07 00:00:00 | 5 | a |
2021-04-07 00:00:00 | 10 | b |
2021-04-07 01:00:00 | 5 | a |
2021-04-07 01:00:00 | 4 | b |
2021-04-08 00:00:00 | 5 | a |
2021-04-08 00:00:00 | 8 | b |
2021-04-08 01:00:00 | 4 | a |
2021-04-08 01:00:00 | 5 | b |
And i want to know how could i sum the values by day and group, like this:
date | total_value | Group |
---|---|---|
2021-04-07 | 10 | a |
2021-04-07 | 14 | b |
2021-04-08 | 9 | a |
2021-04-08 | 13 | b |
Hope someone can help me with this, thanks in advance.
Using the aggregate function sum
and grouping by date
and group
will achieve this. Since you have timestamp data, the solution below casts it to a date
type and groups using that. Finally in the projection, I also casted to a text
to remove the additional date information and just provide with YYYY-MM-DD
Schema (PostgreSQL v11)
CREATE TABLE my_table (
"date" TIMESTAMP,
"value" INTEGER,
"Group" VARCHAR(1)
);
INSERT INTO my_table
("date", "value", "Group")
VALUES
('2021-04-07 00:00:00', '5', 'a'),
('2021-04-07 00:00:00', '10', 'b'),
('2021-04-07 01:00:00', '5', 'a'),
('2021-04-07 01:00:00', '4', 'b'),
('2021-04-08 00:00:00', '5', 'a'),
('2021-04-08 00:00:00', '8', 'b'),
('2021-04-08 01:00:00', '4', 'a'),
('2021-04-08 01:00:00', '5', 'b');
Query #1
select
"date"::date::text,
sum(value) as total_value,
"Group"
FROM
my_table
GROUP BY
"date"::date, "Group";
date | total_value | Group |
---|---|---|
2021-04-07 | 10 | a |
2021-04-07 | 14 | b |
2021-04-08 | 9 | a |
2021-04-08 | 13 | b |