problem with sum function in query

Question

Hi,
I'm trying to obtain some calculations from the data stored in my database. However something is wrong with the query because I know the results given are wrong. I hope I can explain clearly:

I have data in database as follows:

I have a table with time slots (Slots):
SlotID    Slot
=================
1           9-9.30
2           9.30-10
3           10-10.30
4           10.30-11

I have a table with events (Events):
ID Name     SlotID ExtraSlotID Limit1 Limit2
===========================================
1   Event1     1        n/a              20      20
2   Event2     1        n/a              20      20
3   Event3     2        n/a              40      40
4   Event4     2         3                20      20
5   Event5     3         4                40      40
6   Event6     3         4                20      20
7   Event7     3         4                10      10

I'm querying my database to get the total of people attending events in each time slot:
SELECT s.Slot, s.SlotID, ISNULL(SUM(e.Limit1), 0) AS PeopleLimit1, ISNULL(SUM(x.Limit2), 0) AS PeopleLimit2
FROM Slots AS s LEFT OUTER JOIN
Events AS e ON e.Slot = s.SlotID LEFT OUTER JOIN
Events AS x ON x.ExtraSlotID = s.SlotID
GROUP BY s.Slot, s.SlotID

However, this is giving me wrong results (I need this table to give me sum of attendees for each slot as Main slot (PeopleLimit1) and as extra slot (PeopleLimit2) but it seems PeopleLimit2 is in a kind of loop and and sums twice when there are events in that slot but as main slot
SlotID PeopleLimit1 PeopleLimit2
================================
1               40                  0
2               60                  0
3               70                 60 *wrong
4               0                   70

If I use just main slot with this query:
SELECT s.Slot, s.SlotID, ISNULL(SUM(e.Limit1), 0) AS PeopleLimit1
FROM Slots AS s LEFT OUTER JOIN
Events AS e ON e.Slot = s.SlotID
GROUP BY s.Slot, s.SlotID

I get:
SlotID PeopleLimit1
====================
1             40
2             60
3             70
4             0


If I use only the extra slot in my query:
SELECT s.Slot, s.SlotID, ISNULL(SUM(x.Limit2), 0) AS PeopleLimit2
FROM Slots AS s LEFT OUTER JOIN
Events AS x ON x.ExtraSlotID = s.SlotID
GROUP BY s.Slot, s.SlotID

I get this:
SlotID PeopleLimit2
1                0
2                0
3               20 *correct
4               70

The problem is how to put the last two tables together and sum PeopleLimit1 and PeopleLimit2.

Thanks for your help!

Answer 1

Here's WHY you were getting the wrong results.  Compare the first query, which just JOINS Slots and Events, to the second query, which brings in Events one more time.  You can see where the total of 60 comes from.

select *
from Slots s 
left join Events e on s.SlotID=e.SlotID
where s.SlotID=3
/*
SlotID Slot     ID Name   SlotID ExtraSlotID Limit1 Limit2
----------------------------------------------------------
     3 10-10.30  5 Event5      3           4     40     40
     3 10-10.30  6 Event6      3           4     20     20
     3 10-10.30  7 Event7      3           4     10     10
*/     
     
select *
from Slots s 
left join Events e on s.SlotID=e.SlotID
left join Events x on s.SlotID=x.ExtraSlotID
where s.SlotID=3
/*
SlotID Slot     ID Name   SlotID ExtraSlotID Limit1 Limit2 ID Name   SlotID ExtraSlotID Limit1 Limit2
-----------------------------------------------------------------------------------------------------
     3 10-10.30  5 Event5      3           4     40     40  4 Event4      2           3     20     20
     3 10-10.30  6 Event6      3           4     20     20  4 Event4      2           3     20     20
     3 10-10.30  7 Event7      3           4     10     10  4 Event4      2           3     20     20
*/     

Perhaps in this case, you can do 2 correlated subqueries to get the sums instead:

select s.SlotID
      ,s.Slot
      ,PeopleLimit1=(select sum(Limit1)
                     from Events
                     where SlotID=s.SlotID)
      ,PeopleLimit2=(select sum(Limit2)
                     from Events
                     where ExtraSlotID=s.SlotID) 
from Slots s

---

--Brad (My Blog)

Answer 2

Another method to solve this would be to base the join on the two columns and to use CASE expressions to sum only when there is a match:

SELECT S.slotid, S.slot,
       SUM(CASE WHEN S.slotid = E.slotid THEN limit1 ELSE 0 END) AS limit1_total, 
       SUM(CASE WHEN S.slotid = E.extraslotid THEN limit2 ELSE 0 END) AS limit2_total
FROM Slots AS S
JOIN Events AS E
  ON S.slotid = E.slotid
  OR S.slotid = E.extraslotid
GROUP BY S.slotid, S.slot;

---

Plamen Ratchev