SQL query with SUM and GROUP

Question

I have the following table backup which combines data of hours, persons, type of backup and date of backup:

data        |   type   |   persons   |  hours
==============================================
2015-06-12      15           4            8
2015-06-12      15           4            8
2015-06-12      15           4            8
2015-06-12      15           4            8
2015-06-13      14           2            4
2015-06-13      14           2            4

I'm trying to write a query which will return following result:

       data     |   type   |   persons   |  hours
    ==============================================
    2015-06-12      15           4            32
    2015-06-13      14           2            8

My query looks like this:

SELECT "data", "type", SUM("hours"), "persons" FROM "backup" GROUP BY "type", "data", "persons" ORDER BY "data"

but it does'nt work the way I want :(

EDIT:

after using of this query:

SELECT "data", "type", "persons", SUM("hours") AS "hours" FROM "backup" WHERE "project" = '2000030' GROUP BY "type", "data", "persons" ORDER BY "data"

SQL returns this data:

   data    |   type  |  persons  |  hours
==========================================
2011-12-16 | 2003774 |     9     |   12
2011-12-16 | 2003774 |    10     |   12

but there should be:

   data    |   type  |  persons  |  hours
==========================================
2011-12-16 | 2003774 |     9     |   54

In DB I have 9 records (one record per person), in each person cell is 9, and in each hours cell is 9. I need to sum hours per date and per type of backup (support).

I see nothing wrong with the query. What output do you get? . . . — Kamil Gosciminski
– Kamil Gosciminski, Commented Sep 15, 2016 at 21:56
the query you are sharing will give you the desired result you are showing Are you perhaps simplifying it too much that you have eliminated the issue? — Matt
– Matt, Commented Sep 15, 2016 at 21:58

Emil Alkalay · Accepted Answer · 2016-09-15 22:43:06Z

1

The correct query is

SELECT "data", "type", "persons", SUM("hours") FROM "backup" GROUP BY "type", "data", "persons" ORDER BY "data"

"persons" and SUM("hours") are switched. This is the only problem, based on expected result.

update: If you expect to have one row per type try following:

SELECT "data", "type", MAX("persons"), SUM("hours") FROM "backup" GROUP BY "type", "data" ORDER BY "data"

edited Sep 15, 2016 at 22:43

answered Sep 15, 2016 at 22:10

Emil Alkalay

4964 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

kacper Over a year ago

I'm affraid it's not. I also need to use "GROUP BY data, type, persons" part (because of: ERROR: column "data" must appear in the GROUP BY clause or be used in an aggregate function). Then query returns duplicated records, ex.: 2011-12-16 | 2003774 | 9 | 12 and 2011-12-16 | 2003774 | 10 | 12 :(

Emil Alkalay Over a year ago

I have appended missing part to the answer (copy and paste mistake). Do you still have a problem?

kacper Over a year ago

I saw - thanks Emil, but still same problem as I wrote before. I don't know why, but records are duplicated with different persons and hours values. I tried with SELECT DISTINCT but it didn't help.

Emil Alkalay Over a year ago

As I understand by dublicacting you mean you need to have one row per type. If so, you need to decide what would you your aggregate rule for person column. Lets say you need to show maximum, then aggregate with MAX(person). Average is also possible rule in your case.

kacper Over a year ago

I think now it works fine, thanks a lot Emil! I totally forgot about MAX rule. Tomorrow (now is 1AM) I'll check data returned by query. Regs.

|

Collectives™ on Stack Overflow

SQL query with SUM and GROUP

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related