加入后 SUM 不正确 [英] Incorrect SUM after Joining

问题描述

我正在尝试以三种不同的方式从一列中获取数据.我正在处理供应商报价和客户接受的报价(然后成为项目).

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'

到目前为止一切顺利.当我开始加入必要的表格以获取最后一块时，问题就出现了.下面是 singular 查询的样子(并且有效):

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.

感谢您的帮助或指点.

---------------

所以这里有一个查询，它执行必要的连接以尝试在保持 CASE 语句存在的同时获得这么大的总数:

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'

我已经删除了 SUM 部分，看看会发生什么，正如你们中的一些人所建议的那样.果然，我得到了每个值的三个记录.

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?

编辑 #2:------------根据要求，以下是单独的工作查询:

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

年初至今:

/*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

当前活动/打开:

/*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)

推荐答案

Shibormot 可能是对的，您的 OfferSuggestionDetail 在 OfferSuggestionHeader 中的每一行都有多行.当您加入它们时，加入的每一行的 ofh. Savings 字段都是相同的.然后，当你把它加起来时，它会乘以细节行的数量.

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.

-- 编辑为使用您的三个单独的查询:如果您采用查询 2，则可以将其重写为:当前活动/打开:

-- 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 

确认它给了你你想要的.如果是，则它现在具有与查询 2 相同的 FROM 子句和相同的 WHERE 子句，因此它们可以这样组合:

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 支持选择列表中的列，该列本身是一个 SELECT.

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 

希望能让你更近一步.

这篇关于加入后 SUM 不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！