Two SUM queries together

Question

I have a table "Events" and a table “Slots” where Slots.SlotID = Events.MainSlot OR Events.ExtraSlot.

I need to sum the number of “Attendees” per time slot (as MainSlot and as ExtraSlot - ExtraSlot is optional)

Table "Events"

ID------Name----------MainSlot-------ExtraSlot-------Attendees
1-------Event1--------1 -------------n/a-------------20
2-------Event2--------1 -------------n/a-------------20
3-------Event3--------2 -------------n/a-------------40
4-------Event4--------2 -------------3---------------20
5-------Event5--------3 -------------4---------------40
6-------Event6--------3 -------------4---------------20
7-------Event7--------3 -------------4---------------10

Table "Slots"

SlotID--- Slot
1-------- 9.00-9.30
2-------- 9.30-10
3-------- 10.30-10.30
4-------- 10.30-11

If I query the database separately as follows:

         SELECT s.Slot, s.SlotID, ISNULL(SUM(e. Attendees), 0) AS Attendees1
           FROM Slots AS s 
LEFT OUTER JOIN Events AS e ON e.MainSlot = s.SlotID
       GROUP BY s.Slot, s.SlotID

...or:

         SELECT s.Slot, s.SlotID, ISNULL(SUM(x.Attendees), 0) AS Attendees2 
           FROM Slots AS s 
LEFT OUTER JOIN Events AS x ON x.ExtraSlot = s.SlotID 
       GROUP BY s.Slot, s.SlotID

I get the following, respectively:

SlotID ------  Attendees1
1------------- 40
2------------- 60
3------------- 70
4------------- 0

SlotID ------- Attendees2
1------------- 0
2------------- 0
3------------- 20 *correct
4------------- 70

Both results are correct.

However if I put the two queries together something is wrong as shows the table below

SELECT  s.Slot, s.SlotID, ISNULL(SUM(e.Attendees), 0) AS Attendees1,
        ISNULL(SUM(x. Attendees), 0) AS Attendees2
FROM Slots AS s LEFT OUTER JOIN
Events AS e ON e.MainSlot = s.SlotID LEFT OUTER JOIN
Events AS x ON x.ExtraSlot = s.SlotID
GROUP BY s.Slot, s.SlotID

SlotID------------- Attendees1---------- Attendees2
1-------------------40-------------------0
2-------------------60-------------------0
3-------------------70------------------60 *wrong
4-------------------0-------------------70

What am I doing wrong? Thanks for your help!

Answer 1

My guess is that it is something related to the fact that you have records in both for that group. Try the following (MSSQL Server)

SELECT s.Slot, s.SlotID, ISNULL(SUM(e.Attendees), 0) AS Attendees1,
ISNULL(SUM(CASE WHEN  x. Attendees IS NULL THEN 0 ELSE x.Attendees END), 0) AS Attendees2
FROM Slots AS s LEFT OUTER JOIN
Events AS e ON e.MainSlot = s.SlotID LEFT OUTER JOIN
Events AS x ON x.ExtraSlot = s.SlotID
GROUP BY s.Slot, s.SlotID

Answer 2

SELECT  s.SlotId,
        COALESCE(
        (
        SELECT  SUM(attendees)
        FROM    events ea
        WHERE   ea.MainSlot = s.SlotId
        ), 0) AS AttendeesAsMain,
        COALESCE(
        (
        SELECT  SUM(attendees)
        FROM    events ea
        WHERE   ea.ExtraSlot = s.SlotId
        ), 0) AS AttendeesAsExtra
FROM    Slots s

Answer 3

    SELECT a.SlotID, Attendees1, Attendees2 FROM
         (SELECT s.Slot, s.SlotID, ISNULL(SUM(e. Attendees), 0) AS Attendees1
           FROM Slots AS s 
         LEFT OUTER JOIN Events AS e ON e.MainSlot = s.SlotID
           GROUP BY s.Slot, s.SlotID) as a,

         (SELECT s.Slot, s.SlotID, ISNULL(SUM(x.Attendees), 0) AS Attendees2 
           FROM Slots AS s 
         LEFT OUTER JOIN Events AS x ON x.ExtraSlot = s.SlotID 
           GROUP BY s.Slot, s.SlotID) as b
    WHERE a.SlotID = b.SlotID

Answer 4

You're joining twice, so you get a duplicate results in your return. Basically you're getting this:

slotid......e.mainslot......x.extraslot.......x.attendees.....e.attendees

3...........3...............3.................20..............40
3...........3...............3.................20..............20
3...........3...............3.................20..............10

This is the expected behaviour because you're joining X to each resultant row from Slots JOIN E.