Problem with SUM and GROUP BY clause connection
-
Hello friends,
I have an issue with not understanding what happens when I sum and group the fields. I thought that maybe there is an issue with joins of different tables but I ran them separately and everything works just fine. I get correct numbers (what I want to see in the table). However, when I paste SUM into the whole code it starts multiplying fields instead of summing.
Do you have any other ideas what can be wrong but for joins?
Here I am running p.QTY field from PLANARRIV table I get a sane number 22.
Later on, I try to incorporate the same calculation into a bigger code I get a drastically different number
Those four fields are multipled(?), when I sum them one by one they bring the correct data, but when I paste them into the whole code (group everything) numbers are getting thrown away specifically for those 4 columns namely ' Forecast Demand (sales order)' , ' Last Week Actual', 'Forecast Accuracy', ' Instock %'
Calculations 'Inventory On Hand', 'On Order' 'Projected Available Inventory (In DC)' work perfectly.
SELECT s.LOC AS 'DC',
s.ITEM AS 'Item',
d.DESCR As 'Item attributes.Description',
d.U_PRODUCT_CATEGORY As 'Item attributes. Product category',
d.U_MSRP As 'Item attributes. MSRP',
d.U_FORMAT As 'Item attributes. Format',
d.U_ONSALE_DATE AS 'Item attributes. On sale date',
SUM(s.OH) AS 'Inventory On Hand',
SUM(o.SOQ) AS 'On Order',
SUM(P.QTY) AS 'Forecast Demand (sales order)',
SUM(R.QTY) AS 'Last Week Actual',
SUM(p.QTY / r.QTY) AS 'Forecast Accuracy',
SUM(CAST(p.QTY * 100.00 / s.OH AS decimal(5, 2))) AS 'Instock %',
s.OH + o.SOQ AS 'Projected Available Inventory (In DC)'
FROM SCPOMGR.SKU s
JOIN SCPOMGR.DMDUNIT D
ON s.ITEM=D.DMDUNIT
JOIN SCPOMGR.ORDERSKU O
ON s.ITEM=o.ITEM AND s.LOC=o.DEST
JOIN SCPOMGR.PLANARRIV P
ON S.ITEM=P.ITEM AND P.DEST=S.LOC
JOIN SCPOMGR.RECSHIP R
ON S.ITEM=R.ITEM AND S.LOC=R.SOURCE
WHERE s.OHPOST BETWEEN @LastYearLWDATE and @LastWeekDATE
AND s.LOC LIKE 'DC%' AND P.DEST LIKE 'DC%' AND R.SOURCE LIKE 'DC%'
GROUP BY s.LOC,
s.ITEM,
d.DESCR,
d.U_PRODUCT_CATEGORY,
d.U_MSRP,
d.U_FORMAT,
d.U_ONSALE_DATE,
s.OH, o.SOQ
ORDER BY s.LOC, s.ITEM -
I don't think your group by should include
s.OH,
o.SOQ,
-
@homebrew01 I have revised my question, I think I didn't phrase it correctly.
-
Could be data that isn't unique as thought, or maybe the join criteria is missing for some of the tables and creating a cross join that adds multiple records unexpectedly, or maybe the filtering criteria is not accurate. If we had some sample data and the table schemas, someone might be able to figure it out.
-
Just my two cents, but typically, when your SUM aggregates aren't coming out correctly, it's almost always because one of your JOINs is contributing more than one row when it shouldn't be. Also, it's fairly common to make the mistake of summing a value from a table that contributes one row to the overall query, when there are other tables that contribute multiple rows to the overall query, and then there's a SUM on this other table that is correct, but the one that isn't now appears to be the problem, when the actual cause is using SUM on that particular table given that it will end up providing exactly as many rows (mind you, with repeated values) as that other table. Just testing for that possibility will likely reveal the source of your grief.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply