1

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.

  • 1
    Regarding your last edit and the question: read the answers you got! The number of rows returned depends on the number of groups which depends on the GROUP BY list. You GROUP BY date, you get as many rows as the different dates, you GROUP BY insp_id, you get as many rows as the different insp_id values. You GROUP BY a,b, you get as many rows as the different (a,b) combinations. – ypercubeᵀᴹ Feb 8 '18 at 20:20
  • @ypercubeᵀᴹ - Right. It was a dumb question. I wish I could delete it. – Wilson Feb 8 '18 at 20:29
  • 2
    Well, you wouldn't have cleared what happens then, would you? – ypercubeᵀᴹ Feb 8 '18 at 20:31
  • 3
    @ypercubeᵀᴹ - Fair enough. I think my problem/misunderstanding stems back to MS Access. In Access, the query design wizard lays out the group by and aggregates all in a single 'Totals row', so-to-speak. This had led me to believe that the aggregate was some type of group by, which is obviously false. Screencap here. – Wilson Feb 8 '18 at 21:01
  • 2
    @Wilson Nothing wrong with making those assumptions based on your experience with Access. Good on you for taking the initiative to learn the distinction between the two platforms and dig down into the "why" instead of just blindly coding away. – MguerraTorres Feb 9 '18 at 16:34
3

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.

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

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.