Sum of column breaking in group by of three-table join

Question

I am having a problem with a three-table join where the sum of a field gets broken when I add a field from the third table in the group by clause:

-- First join returns 100 rows which are all the Employee's in table

SELECT Employee.First_Name,
       Employee.Last_Name,
       Employee.Emp_ID,
       SUM(WorkSchedule.Hours_Worked) Hours_Worked
FROM   Employee
INNER JOIN WorkSchedule
ON Employee.Emp_ID = WorkSchedule.Emp_ID
GROUP BY 
       Employee.First_Name,
       Employee.Last_Name,
       Employee.Emp_ID

The output:

First_Name  Last_Name   Emp_ID  Hours_Worked
Laura       Dorrity     1       63.00
Aube        Habershaw   2       70.97
Tessy       Goding      3       61.52
Ana         Kilroy      4       29.01
Ardella     Sprowson    5       48.12

But the second join breaks the sum:

-- Should also return 100 rows - returns 646

SELECT Store.Store_Name,
       Employee.First_Name,
       Employee.Last_Name,
       Employee.Emp_ID,
       SUM(WorkSchedule.Hours_Worked) Hours_Worked
FROM   Employee
INNER JOIN WorkSchedule
ON Employee.Emp_ID = WorkSchedule.Emp_ID
INNER JOIN Store
ON WorkSchedule.Store_ID = Store.Store_ID
GROUP BY 
       Employee.First_Name,
       Employee.Last_Name,
       Employee.Emp_ID,
       Store.Store_Name

The output:

Store_Name      First_Name      Last_Name   Emp_ID  Hours_Worked
Mycat           Christa         Bruno       77      9.54
Jabbercube      Elisha          Siley       54      2.50
Riffwire        Evanne          Whifen      62      8.95
Thoughtworks    Laura           Dorrity     1       2.86

Which is obviously not the desired output. I've tried changing the type of joins but to no effect. The tables:

The Employee Table

The Store Table

The WorkSchedule Table

Any help is appreciated, thanks.

K

UPDATE:

-- Employee

Emp_ID  First_Name  MI      Last_Name   Address1            Address2    City        State_CD    Zip_code    Zip_Ext Email                       Phone       Hourly_Salary   Active_IND  CREATE_BY   CREATE_DT           UPDATE_BY   UPDATE_DT
1       Laura       NULL    Dorrity     9263 Towne Street   NULL        Jackson     TN          38308       NULL    ldorrity0@trellian.com      NULL        15.75       1           User        2019-11-06 19:49:59.750 User        2019-11-06 19:49:59.750
2       Aube        M       Habershaw   70 Jenna Avenue     NULL        Miami       FL          33185       NULL    NULL                        13059196714 15.75       1           User        2019-11-06 19:49:59.750 User        2019-11-06 19:49:59.750
3       Tessy       F       Goding      7 Maywood Center    NULL        Portsmouth  NH          03804       NULL    tgoding2@1und1.de           16039019562 15.75       1           User        2019-11-06 19:49:59.750 User        2019-11-06 19:49:59.750
4       Ana         NULL    Kilroy      2003 Sachs Crossing NULL        Louisville  KY          40298       NULL    akilroy3@thetimes.co.uk     NULL        15.75       1           User        2019-11-06 19:49:59.750 User        2019-11-06 19:49:59.750
5       Ardella     F       Sprowson    40290 Kipling Alley NULL        Raleigh     NC          27658       NULL    NULL                        NULL        15.75       1           User        2019-11-06 19:49:59.750 User        2019-11-06 19:49:59.750


-- Store

Store_ID    Store_Name      Manager_ID      Address1                Address2    City                State_CD    Zip_code    Zip_Ext Email                               Phone       Active_IND  CREATE_BY   CREATE_DT               UPDATE_BY   UPDATE_DT
1           Riffwire        47              73768 Forest Run Plaza  NULL        New York City       NY          10280       NULL    shedges0@pagespersoorange.fr        13474826752 1           User        2019-11-06 19:49:59.757 User        2019-11-06 19:49:59.757
2           Thoughtworks    94              4 Scofield Trail        NULL        Van Nuys            CA          91406       NULL    alarge5@plala.or.jp                 16265775586 1           User        2019-11-06 19:49:59.757 User        2019-11-06 19:49:59.757
3           Thoughtstorm    24              40642 Schlimgen Lane    NULL        Indianapolis        IN          46254       NULL    kcanarioc@cargocollective.com       13175466135 1           User        2019-11-06 19:49:59.757 User        2019-11-06 19:49:59.757
4           Jabbersphere    91              7330 Pepper Wood Circle NULL        Lakeland            FL          33805       NULL    msilversmidtj@constantcontact.com   18631093285 1           User        2019-11-06 19:49:59.757 User        2019-11-06 19:49:59.757
5           Brainlounge     100             2 Ludington Pass        NULL        Saint Petersburg    FL          33715       NULL    fmynottw@acquirethisname.com        17273590553 1           User        2019-11-06 19:49:59.757 User        2019-11-06 19:49:59.757


-- WorkSchedule

WSID    Emp_ID  Store_ID    ShiftDate           Hours_Worked    CheckDate   Active_IND  CREATE_BY   CREATE_DT   UPDATE_BY   UPDATE_DT
1       37      3           2019-11-13 00:00:00.000 6.81        NULL        NULL        NULL        NULL        NULL        NULL
2       64      5           2019-11-14 00:00:00.000 7.29        NULL        NULL        NULL        NULL        NULL        NULL
3       23      6           2019-11-14 00:00:00.000 2.09        NULL        NULL        NULL        NULL        NULL        NULL
4       45      7           2019-11-13 00:00:00.000 4.20        NULL        NULL        NULL        NULL        NULL        NULL
5       68      5           2019-11-10 00:00:00.000 4.99        NULL        NULL        NULL        NULL        NULL        NULL
6       8       4           2019-11-11 00:00:00.000 7.14        NULL        NULL        NULL        NULL        NULL        NULL
7       37      6           2019-11-12 00:00:00.000 6.83        NULL        NULL        NULL        NULL        NULL        NULL

UPDATE FOR ANSWER:

Thanks to LukStorms for his troubleshooting and answer - He was correct in his judgement for checking if all the Store_ID's were there:

select count(Store_ID) from WorkSchedule; -- no nulls

...and if there were more than one store the employee could be working at:

select Emp_ID,Store_ID from WorkSchedule order by Emp_ID; -- one to many

The corrected query:

SELECT STRING_AGG(Store.Store_Name, ', ') WITHIN GROUP (ORDER BY Store.Store_Name) AS StoreNames,-- Store.Store_Name,
       Employee.First_Name,
       Employee.Last_Name,
       Employee.Emp_ID,
       SUM(WorkSchedule.Hours_Worked) Hours_Worked
FROM   Employee
INNER JOIN WorkSchedule
ON Employee.Emp_ID = WorkSchedule.Emp_ID
LEFT JOIN Store
ON WorkSchedule.Store_ID = Store.Store_ID
GROUP BY 
       Employee.First_Name,
       Employee.Last_Name,
       Employee.Emp_ID

Thanks, it nice to see people will still help a newb...

Perhaps you're after a `LEFT JOIN`? Perhaps you're after a `LEFT JOIN` and pre-aggregates valies. Impossible to know with no sample data. — Thom A, Nov 17 '19 at 19:50

LukStorms · Accepted Answer · 2019-11-17T21:14:26.580

3

There are probably 2 issues with joining that extra table.

1) there are probably workschedules without the store_id filled in.

2) the workschedules per employee are not neccesairly all for the same store.

To solve the first issue use a LEFT JOIN on the Store table instead of the current INNER JOIN.

To solve the second issue, keep the GROUP BY only on the Employee fields.

GROUP BY Employee.Emp_ID, Employee.First_Name, Employee.Last_Name

And then aggregate for the Store_Name.
For example (if your version supports it) the STRING_AGG function.

STRING_AGG(Store.Store_Name, ', ') WITHIN GROUP (ORDER BY Store.Store_Name) AS StoreNames

edited Nov 17 '19 at 21:14

answered Nov 17 '19 at 21:08

LukStorms

28,916
5
31
45

I did a count of Store_ID's and they're all there which should take care of problem one, as for problem two could you explain further? I will google and try the STRING_AGG() function. Wish me luck!! – kn0t Nov 17 '19 at 22:26
Are these strings the Store_Names where each employee works? If so you're an f'ing genius!! Thanks. – kn0t Nov 17 '19 at 22:37
@kn0t Glad that it helped you. :) And you're in luck that you work with a recent version of MS Sql Server. Without STRING_AGG such query would be more complicated. For example in [this old example](https://stackoverflow.com/a/50599822/4003419). – LukStorms Nov 18 '19 at 16:18

Sum of column breaking in group by of three-table join

1 Answers1