Beginner SQL: Unexpected results from GROUP BY

Question

I have a sidewalk_deficiency table:

create table sidewalk_deficiency (
    def_id number(10), 
    insp_id number(10),
    def_length number(10,2));
insert into sidewalk_deficiency values (10,1,.5);
insert into sidewalk_deficiency values (11,1,1);
insert into sidewalk_deficiency values (12,1,1.5);
insert into sidewalk_deficiency values (13,2,2);
insert into sidewalk_deficiency values (14,2,2.5);
commit;

    DEF_ID    INSP_ID  DEF_LENGTH
---------- ----------  ----------
        10          1          .5
        11          1           1
        12          1         1.5
        13          2           2
        14          2         2.5

I can create a query that summarizes the sidewalk inspections by tallying up the deficiency lengths, per inspection:

select 
    insp_id,
    sum(def_length) as def_length 
from 
    sidewalk_deficiency 
group by 
    insp_id

   INSP_ID DEF_LENGTH
---------- ----------
         1          3
         2        4.5

This works as I had expected. Two rows are output; one for each insp_id.

---

I understand that the above query does, in fact, provide the desired output. However, I don't really understand why it works the way it does.

Admittedly, when I write queries like this, it makes more sense to me to include fields such as def_length in both the aggregate function and the group by clause. To me, I know I want to collapse the def_length into a sum, so it seems to me that I should group by it.

select 
    insp_id,
    sum(def_length) as def_length 
from 
    sidewalk_deficiency 
group by 
    insp_id,
    def_length       --<<--Problem

   INSP_ID DEF_LENGTH
---------- ----------
         2        2.5
         1         .5
         1          1
         2          2
         1        1.5

Of course, as we can see, my thought process is flawed. When I include the def_length field in the group by, it produces more records than I had expected.

Forgive my ignorance, but why does the second query produce more results than the first? I would have thought that including the field in both the aggregate function and the group by would have grouped the rows as I had planned.

Answer 1

This answer is a bit unlike the others, but I understood your question to be more related to "what is the logic behind adding/removing columns to/from the SELECT and GROUP BY". If it's out of place, please let me know and I'll delete.

It's not really a quirk, it's just a basic rule of aggregation. If I'm counting people that live at my house (3 total), I count people that live at my address. So I can say

SELECT Address
      ,COUNT(PersonID) AS PeopleCount
FROM Household
GROUP BY Address

That will give me the number of people at my address.

If I try to do it by name:

SELECT Address
       Name
      ,COUNT(PersonID) AS PeopleCount
FROM Household
GROUP BY Address
        ,Name

Then it will give me a "1" value in People count as there is only 1 person per address per name in my household.

If I had 2 people with the same name living at my house, then the "PeopleCount" value for those two people would be "2" until I found something else to add to make the records unique.

So the reason you add items to the SELECT/GROUP BY is to perform aggregates based on UNIQUE records. The more columns you add to the SELECT statement, the more unique the records are.

Answer 2

In fact, it runs as expected.

If you remove aggregate sum(), you see what groups will by added:

select 
    insp_id, def_length
from 
    sidewalk_deficiency 
group by 
    insp_id,
    def_length 

INSP_ID | DEF_LENGTH
------: | ---------:
      2 |        2.5
      1 |         .5
      1 |          1
      2 |          2
      1 |        1.5

In this case there aren't two rows with the same id, def_length.

But if you add a duplicated row:

insert into sidewalk_deficiency values (14,2,2.5);

1 rows affected

select 
    insp_id, def_length, sum(def_length) as sm
from 
    sidewalk_deficiency sd
group by 
    insp_id,
    def_length 

Then you get 5 for the row where insp_id = 2, def_length = 2.5

INSP_ID | DEF_LENGTH |  SM
------: | ---------: | --:
      2 |        2.5 |   5
      1 |         .5 |  .5
      1 |          1 |   1
      2 |          2 |   2
      1 |        1.5 | 1.5

dbfiddle here

Answer 3

Change the query to include all group by clauses and you will see that it is showing you the expected results. Since you are grouping by def_length and the def_length is always different for each entry it will treat the as separate groups.

If you are trying to get an aggregate value of a column it should never be in the group by clause as it will always group together rows where the value matches which makes the aggregate function pointless.

select 
    insp_id,
    def_length,
    sum(def_length) 
from 
    sidewalk_deficiency 
group by 
    insp_id,
    def_length

   INSP_ID DEF_LENGTH SUM(DEF_LENGTH)
---------- ---------- ----------
     2        2.5        2.5
     1         .5         .5
     1          1          1
     2          2          2
     1        1.5        1.5