CODEWITHSUNDEEP
sqlpostgresqldatetimesum
Lautaro Aguilera
Lautaro Aguilera

Reputation: 63

How to Sum values by day and group in postgresql?

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.

Upvotes: 1

Views: 2439

Answers (1)

ggordon
ggordon

Reputation: 10035

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

View on DB Fiddle

Upvotes: 3

Related Questions