Incorrect SUM after Joining

Question

I'm trying to grab data from one column in three different ways. I am working with vendor offers and customer accepted offers (which then become projects).

I'd like to get the total savings amount from all of their projects ever, completed or not. Secondly, we are grabbing the current savings, ie the savings from the currently open projects. Lastly, I need to find the total amount being offered to them that they have yet to act upon.

I have the first two taken care of with this query:

SELECT SUM(osh.Savings) as YTD,
       SUM(case when p.current_status < 3 then Savings end) AS "Open Savings"
FROM OfferSuggestionHeader osh LEFT JOIN
     Projects p
     ON p.offer_id = osh.OfferID
WHERE p.uid = '1'

So far so good. The issue comes up when I start joining the necessary tables to grab that last piece. Here's what the singular query looks like (and it works):

SELECT SUM(ofh.Savings)
from OfferSuggestionHeader ofh
LEFT JOIN OfferSuggestionDetail osd
on ofh.OfferID = osd.OfferID
LEFT JOIN Facilities f
on osd.FacilityID = f.id
LEFT JOIN UserFacility uf
on f.id = uf.fid
LEFT JOIN Users u
on uf.uid = u.uid
WHERE u.uid = 1
AND ofh.OfferID NOT IN(SELECT offer_id FROM Projects WHERE uid = 1)

I've tried half a dozen different ways of mixing them, all of which ended unsuccessfully. It seems like it is adding up the numbers multiple times or something that like that.

Thanks for any help or pointers.

EDIT: ---------------

So here's a query that does the necessary joins to try to get that big total while keeping the CASE statements present:

SELECT (osh.Savings) as "Potential", 
    (case when p.current_status < 3 then osh.Savings else null end) AS "Open Savings",
    (case when p.uid=1 then osh.Savings else null end) AS "YTD"
FROM OfferSuggestionHeader osh
LEFT JOIN OfferSuggestionDetail osd
    ON osd.OfferID = osh.OfferID
LEFT JOIN UserFacility uf
    ON uf.fid = osd.FacilityID
LEFT JOIN Projects p
    ON p.uid = uf.uid
WHERE p.uid = '1'

I have removed the SUM portion to see what happens as some of you had suggested. Sure enough, I get a three records for each value.

Does this help clarify the issue better?

EDIT #2: ------------ As requested, here are the individual working queries:

YTD:

/*YTD Savings*/
SELECT SUM(osh.Savings) as YTD
FROM Projects p
LEFT JOIN OfferSuggestionHeader osh
    ON p.offer_id = osh.OfferID
WHERE p.uid = 1

Currently Active/Open:

/*total from open projects*/
SELECT SUM(osh.Savings) as TotalOpen
FROM Projects p
LEFT JOIN OfferSuggestionHeader osh
    ON p.offer_id = osh.OfferID
WHERE p.uid = 1 AND p.current_status < 3

And, lastly, the sum of potential savings:

/*POTENTIAL*/
SELECT SUM(ofh.Savings) as Potential
from OfferSuggestionHeader ofh
LEFT JOIN OfferSuggestionDetail osd
on ofh.OfferID = osd.OfferID
LEFT JOIN Facilities f
on osd.FacilityID = f.id
LEFT JOIN UserFacility uf
on f.id = uf.fid
LEFT JOIN Users u
on uf.uid = u.uid
WHERE u.uid = 1
AND ofh.OfferID NOT IN(SELECT offer_id FROM Projects WHERE uid = 1)

Answer 1

Shibormot is probably right that your OfferSuggestionDetail has multiple rows for each row in OfferSuggestionHeader. When you join them, the ofh.savings field is the same on each row of the join. Then, when you sum that up, it gets multiplied by the number of detail-lines.

-- Edited to use your three separate queries: If you take query 2, it can be re-written thus: Currently Active/Open:

SELECT SUM(CASE WHEN p.current_status < 3 osh.Savings ELSE 0 END ) as TotalOpen
FROM Projects p
LEFT JOIN OfferSuggestionHeader osh
    ON p.offer_id = osh.OfferID
WHERE p.uid = 1 

Confirm that it gives you what you want. If it does, it now has the same FROM clause and the same WHERE clause as Query 2, so they can be combined thus:

SELECT SUM(osh.Savings) as YTD
      ,SUM(CASE WHEN p.current_status < 3 osh.Savings ELSE 0 END ) as TotalOpen
FROM Projects p
LEFT JOIN OfferSuggestionHeader osh
    ON p.offer_id = osh.OfferID
WHERE p.uid = 1 

MySql supports a column in a select list that is itself a SELECT.

If I slip your third query into the combined one above, I'd get this: (One can probably clean up that inner one a bit, but it ought to work.)

SELECT SUM(osh.Savings) as YTD
      ,SUM(CASE WHEN p.current_status < 3 osh.Savings ELSE 0 END ) as TotalOpen

, (SELECT SUM(ofh2.Savings) as Potential
   from OfferSuggestionHeader ofh2
   LEFT JOIN OfferSuggestionDetail osd2 on ofh2.OfferID = osd2.OfferID
   LEFT JOIN Facilities f2 on osd2.FacilityID = f2.id
   LEFT JOIN UserFacility uf2 on f2.id = uf2.fid
   LEFT JOIN Users u on uf2.uid = u2.uid
   WHERE ofh.OfferID = ofh2.OfferID 
   AND u2.uid = 1
   AND ofh2.OfferID NOT IN(SELECT offer_id FROM Projects WHERE uid = 1
  )


FROM Projects p
LEFT JOIN OfferSuggestionHeader osh
    ON p.offer_id = osh.OfferID
WHERE p.uid = 1 

Hope that takes you one step closer.