locked
problem with sum function in query RRS feed

  • 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!
    • Moved by Alex Feng (SQL) Wednesday, November 4, 2009 7:22 AM about t-sql statement (From:SQL Server Spatial)
    Monday, November 2, 2009 4:21 PM

Answers

  • 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)
    • Proposed as answer by Naomi N Wednesday, November 4, 2009 5:51 PM
    • Marked as answer by Zongqing Li Monday, November 9, 2009 7:05 AM
    Wednesday, November 4, 2009 5:40 PM
  • 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
    • Proposed as answer by Brad_Schulz Wednesday, November 4, 2009 7:45 PM
    • Marked as answer by Zongqing Li Monday, November 9, 2009 7:05 AM
    Wednesday, November 4, 2009 7:26 PM

All replies

  • 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)
    • Proposed as answer by Naomi N Wednesday, November 4, 2009 5:51 PM
    • Marked as answer by Zongqing Li Monday, November 9, 2009 7:05 AM
    Wednesday, November 4, 2009 5:40 PM
  • 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
    • Proposed as answer by Brad_Schulz Wednesday, November 4, 2009 7:45 PM
    • Marked as answer by Zongqing Li Monday, November 9, 2009 7:05 AM
    Wednesday, November 4, 2009 7:26 PM