Issue with the SUM function

Question

Post reply

Issue with the SUM function

JeremyU

SSChasing Mays

Points: 637

September 16, 2021 at 11:50 pm

#3930342

Hello friends,

I have one question. It may be a little bit hard to explain but I will try my best. Let me know if my explanation is confusing.

Right now I have a query that has a chain name and lots of repetitions of the same information. I am 99,9% sure that the reason for this is because I am taking Inventory On Hand and On order information from a different table that is not based on a chain level but based on a store level.

After doing some research, I figured out that I just need to sum two columns: sum(Inventory On Hand) and sum(On Order) and it should sum the result based on Chain level and not based on store level.

Issue1

Here is my code

DECLARE @CurrentDateTime DATETIME = GETDATE();
DECLARE @CurrentDate DATE = @CurrentDateTime;

DECLARE @LastWeekDt DATE = DATEADD(WW, -1, DATEADD(DD, 7 - DATEPART(dw, @CurrentDate), @CurrentDate));
DECLARE @LastWeekDATE VARCHAR(8) = convert(varchar(8),cast (@LASTWEEKDT as date),112);

DECLARE @LastYearLWDt DATE = DATEADD(WW, -52, DATEADD(DD, 7 - DATEPART(dw, @LastWeekDt), @LastWeekDt));
DECLARE @LastYearLWDATE VARCHAR(8) = convert(varchar(8),cast (@LastYearLWDt as date),112);




SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF



SELECT F.LOC AS 'Chain', F.DMDUNIT 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',
  
  s.OH AS 'Inventory On Hand', R.QTY AS 'On Order', F.TOTFCST AS 'Forecast Demand (POS)', F.TOTHIST AS 'Last Week Actual', 
  
  FORMAT(f.TOTHIST / (f.TOTFCST - f.TOTHIST) - 1, 'P0') AS 'Forecast Accuracy', 
  CAST(f.TOTFCST * 100.00 / s.OH AS decimal(5, 2)) AS 'Instock %', s.OH + R.QTY AS 'Projected Available Inventory'

  


  FROM SCPOMGR.FCSTPERFSTATIC AS F
  JOIN SCPOMGR.DMDUNIT D
  ON F.DMDUNIT=D.DMDUNIT
  JOIN SCPOMGR.LOC L
  ON F.LOC=L.U_CHAINNAME
  JOIN SCPOMGR.SKU S
  ON S.LOC=L.LOC
  JOIN SCPOMGR.RECSHIP R
  ON R.DEST=L.LOC
  WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE 
  AND S.LOC LIKE 'ST%' AND l.LOC LIKE 'ST%' AND R.DEST LIKE 'ST%';

When I try to sum and type it with a sum operator I get this issue

Issue2

Does someone know how can I sum those two columns and not get any errors so that Inventory on hand and On Order is not based on store level but based on chain level.

Please let me know if you know. Thank you!!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100948 More actions · Answer 1

Best is to move the SUM() into a derived table, like so:

SELECT F.LOC AS 'Chain', F.DMDUNIT 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',
  
  s.[Inventory On Hand], R.[On Order], F.TOTFCST AS 'Forecast Demand (POS)', F.TOTHIST AS 'Last Week Actual', 
  
  FORMAT(f.TOTHIST / (f.TOTFCST - f.TOTHIST) - 1, 'P0') AS 'Forecast Accuracy', 
  CAST(f.TOTFCST * 100.00 / s.[Inventory On Hand] AS decimal(5, 2)) AS 'Instock %', 
  s.[Inventory On Hand] + R.[On Order] AS 'Projected Available Inventory'


  FROM SCPOMGR.FCSTPERFSTATIC AS F
  JOIN SCPOMGR.DMDUNIT D
  ON F.DMDUNIT=D.DMDUNIT
  JOIN SCPOMGR.LOC L
  ON F.LOC=L.U_CHAINNAME
  JOIN ( --<<--
      SELECT S.LOC, SUM(S.OH) AS 'Inventory On Hand'
      FROM SCPOMGR.SKU S
      WHERE S.LOC LIKE 'ST%'
      GROUP BY S.LOC ) AS S
  ON S.LOC=L.LOC
  JOIN ( --<<--
      SELECT R.LOC, SUM(R.QTY) AS 'On Order'
      FROM SCPOMGR.RECSHIP R
      WHERE R.DEST LIKE 'ST%'
      GROUP BY R.DEST ) AS R --<<--Edit: Corrected "R.LOC" to "R.DEST"
  ON R.DEST=L.LOC
  WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE 
  AND l.LOC LIKE 'ST%'

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

JeremyU SSChasing Mays Points: 637 More actions · Answer 2

@scottpletcher

Thank you very much for the response. I get this error. Do you know how it can be fixed?

Issue3

This reply was modified 2 years, 7 months ago by JeremyU.

JeremyU SSChasing Mays Points: 637 More actions · Answer 3

I am not an expert, and trying to understand if I am summing correctly. Do you think it is correct?

DECLARE @CurrentDateTime DATETIME = GETDATE();
DECLARE @CurrentDate DATE = @CurrentDateTime;

DECLARE @LastWeekDt DATE = DATEADD(WW, -1, DATEADD(DD, 7 - DATEPART(dw, @CurrentDate), @CurrentDate));
DECLARE @LastWeekDATE VARCHAR(8) = convert(varchar(8),cast (@LASTWEEKDT as date),112);

DECLARE @LastYearLWDt DATE = DATEADD(WW, -52, DATEADD(DD, 7 - DATEPART(dw, @LastWeekDt), @LastWeekDt));
DECLARE @LastYearLWDATE VARCHAR(8) = convert(varchar(8),cast (@LastYearLWDt as date),112);




SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

SELECT F.LOC AS 'Chain', F.DMDUNIT 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',
  
  s.[Inventory On Hand], R.[On Order], F.TOTFCST AS 'Forecast Demand (POS)', F.TOTHIST AS 'Last Week Actual', 
  
  FORMAT(f.TOTHIST / (f.TOTFCST - f.TOTHIST) - 1, 'P0') AS 'Forecast Accuracy', 
  CAST(f.TOTFCST * 100.00 / s.[Inventory On Hand] AS decimal(5, 2)) AS 'Instock %', 
  s.[Inventory On Hand] + R.[On Order] AS 'Projected Available Inventory'


  FROM SCPOMGR.FCSTPERFSTATIC AS F
  JOIN SCPOMGR.DMDUNIT D
  ON F.DMDUNIT=D.DMDUNIT
  JOIN SCPOMGR.LOC L
  ON F.LOC=L.U_CHAINNAME
  JOIN ( --<<--
      SELECT S.LOC, SUM(S.OH) AS 'Inventory On Hand'
      FROM SCPOMGR.SKU S
      WHERE S.LOC LIKE 'ST%'
      GROUP BY S.LOC ) AS S
  ON S.LOC=L.LOC
  JOIN ( --<<--
      SELECT R.DEST, SUM(R.QTY) AS 'On Order'
      FROM SCPOMGR.RECSHIP R
      WHERE R.DEST LIKE 'ST%'
      GROUP BY R.DEST ) AS R 
  ON R.DEST=L.LOC
  WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE 
  AND l.LOC LIKE 'ST%'

This reply was modified 2 years, 7 months ago by JeremyU.

JeremyU SSChasing Mays Points: 637 More actions · Answer 4

After I typed the code, I still get the same repetitions as before. Cannot understand what keeps it at a store level and not a chain level.

Issue7

Steve Collins SSCrazy Points: 2068 More actions · Answer 5

Maybe the INNER JOIN between SCPOMGR.FCSTPERFSTATIC AS F and SCPOMGR.DMDUNIT AS D on the 'DMDUNIT' column could be causing row expansion. Would you expect this query to return zero rows?

SELECT F.LOC AS [Chain], count(*) row_count
FROM SCPOMGR.FCSTPERFSTATIC AS F
     JOIN SCPOMGR.DMDUNIT D ON F.DMDUNIT=D.DMDUNIT
group by F.LOC
having count(*)>1;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

JeremyU SSChasing Mays Points: 637 More actions · Answer 6

It returns 234 rows. Sounds much more like truth.

Do you have any ideas?

Issue8

Steve Collins SSCrazy Points: 2068 More actions · Answer 7

It's not really a conclusive test. Maybe you're missing a JOIN condition on location? Is there a column in the SCPOMGR.DMDUNIT table which correlates to the 'LOC' column in the SCPOMGR.FCSTPERFSTATIC table? To enable the JOIN to be appropriate on ('DMDUNIT', 'LOC') pairs maybe the SCPOMGR.DMDUNIT table requires summarization first? Scott's recommendations seemed like good ones also imo

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

JeremyU SSChasing Mays Points: 637 More actions · Answer 8

Well, the table SCPOMGR.DMDUNIT doesn't have a location column. It is a table that is basically just provides the item number and its description. There is no LOC information there.

Issue9

As for Scotts comments, thank you by the way Scott! , I have adjusted the code, the result is still broken into a store level and not chain level.

Full code that I have right now

DECLARE @CurrentDateTime DATETIME = GETDATE();
DECLARE @CurrentDate DATE = @CurrentDateTime;

DECLARE @LastWeekDt DATE = DATEADD(WW, -1, DATEADD(DD, 7 - DATEPART(dw, @CurrentDate), @CurrentDate));
DECLARE @LastWeekDATE VARCHAR(8) = convert(varchar(8),cast (@LASTWEEKDT as date),112);

DECLARE @LastYearLWDt DATE = DATEADD(WW, -52, DATEADD(DD, 7 - DATEPART(dw, @LastWeekDt), @LastWeekDt));
DECLARE @LastYearLWDATE VARCHAR(8) = convert(varchar(8),cast (@LastYearLWDt as date),112);

SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

SELECT 
F.LOC AS 'Chain', 
F.DMDUNIT 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',
s.[Inventory On Hand], 
R.[On Order], 
F.TOTFCST AS 'Forecast Demand (POS)', 
F.TOTHIST AS 'Last Week Actual', 
FORMAT(f.TOTHIST / (f.TOTFCST - f.TOTHIST) - 1, 'P0') AS 'Forecast Accuracy', 
CAST(f.TOTFCST * 100.00 / s.[Inventory On Hand] AS decimal(5, 2)) AS 'Instock %', 
s.[Inventory On Hand] + R.[On Order] AS 'Projected Available Inventory'

FROM SCPOMGR.FCSTPERFSTATIC AS F
  JOIN SCPOMGR.DMDUNIT D
  ON F.DMDUNIT=D.DMDUNIT
JOIN SCPOMGR.LOC L
  ON F.LOC=L.U_CHAINNAME
JOIN (
      SELECT S.LOC, SUM(S.OH) AS 'Inventory On Hand'
      FROM SCPOMGR.SKU S
      WHERE S.LOC LIKE 'ST%'
      GROUP BY S.LOC ) AS S
  ON S.LOC=L.LOC
JOIN ( 
      SELECT R.DEST, SUM(R.QTY) AS 'On Order'
      FROM SCPOMGR.RECSHIP R
      WHERE R.DEST LIKE 'ST%'
      GROUP BY R.DEST ) AS R 
  ON R.DEST=L.LOC
WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE 
  AND l.LOC LIKE 'ST%'

Issue10

JeremyU SSChasing Mays Points: 637 More actions · Answer 9

I start guessing, I need to sum all the fields

4) Inventory On Hand 5) On Order 6)Forecast Demand (POS) 7) Last Week Actual 8) Forecast Accuracy 9) Instock % 10) Projected Available Inventory (in store)

so that grouping is done only by Chain and Item level.

Trying to understand how I can adjust the code so that it sums all those columns.

DECLARE @CurrentDateTime DATETIME = GETDATE();
DECLARE @CurrentDate DATE = @CurrentDateTime;

DECLARE @LastWeekDt DATE = DATEADD(WW, -1, DATEADD(DD, 7 - DATEPART(dw, @CurrentDate), @CurrentDate));
DECLARE @LastWeekDATE VARCHAR(8) = convert(varchar(8),cast (@LASTWEEKDT as date),112);

DECLARE @LastYearLWDt DATE = DATEADD(WW, -52, DATEADD(DD, 7 - DATEPART(dw, @LastWeekDt), @LastWeekDt));
DECLARE @LastYearLWDATE VARCHAR(8) = convert(varchar(8),cast (@LastYearLWDt as date),112);

SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

SELECT 
F.LOC AS 'Chain', 
F.DMDUNIT 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',
s.[Inventory On Hand], 
R.[On Order], 
F.TOTFCST AS 'Forecast Demand (POS)', 
F.TOTHIST AS 'Last Week Actual', 
FORMAT(f.TOTHIST / (f.TOTFCST - f.TOTHIST) - 1, 'P0') AS 'Forecast Accuracy', 
CAST(f.TOTFCST * 100.00 / s.[Inventory On Hand] AS decimal(5, 2)) AS 'Instock %', 
s.[Inventory On Hand] + R.[On Order] AS 'Projected Available Inventory'

FROM SCPOMGR.FCSTPERFSTATIC AS F
  JOIN SCPOMGR.DMDUNIT D
  ON F.DMDUNIT=D.DMDUNIT
JOIN SCPOMGR.LOC L
  ON F.LOC=L.U_CHAINNAME
JOIN (
      SELECT S.LOC, SUM(S.OH) AS 'Inventory On Hand'
      FROM SCPOMGR.SKU S
      WHERE S.LOC LIKE 'ST%'
      GROUP BY S.LOC ) AS S
  ON S.LOC=L.LOC
JOIN ( 
      SELECT R.DEST, SUM(R.QTY) AS 'On Order'
      FROM SCPOMGR.RECSHIP R
      WHERE R.DEST LIKE 'ST%'
      GROUP BY R.DEST ) AS R 
  ON R.DEST=L.LOC
WHERE STARTDATE BETWEEN @LastYearLWDATE and @LastWeekDATE 
  AND l.LOC LIKE 'ST%'

jschmidt 17654 SSCommitted Points: 1610 More actions · Answer 10

If you add the table definitions and some sample data, this might be an easily solved question. Guessing at table contents/structure makes it very difficult to guess the correct direction.