How to sum on one value and not to sum on another value

Question

Post reply

How to sum on one value and not to sum on another value

JeremyU

SSChasing Mays

Points: 640
More actions
December 12, 2021 at 4:52 am

#3962782
I have such a situation. I have a query where I want to some one field and not to sum another (maybe subquery might work?)
I want to sum On Order column but not to sum Inventory On Hand.
This is how my table looks right now

On order comes from [SCHEDRCPTS] where there are two lines for each product (just some SEQNUM difference but they should be together like for item 9780593189689 it should be 12 + 4068 = 4080.
Inventory on hand comes from [SKU] where I just need to pull one line 701.
When I type SUM and remove On Order from GROUP BY the number goes * 5 and I get 20400 (that is probably because I have 5 startdates in my main table from where I pull item numbers).
So my question how can i combine two On Order lines (that come from one table so that I get 12 + 4068) for On Order (without SUM/ GROUP BY) because it times everyhting by 5. And just leave Inventory On Hand not touched without summing so that I can have one line (like here 701) without summing.
I read somewhere that maybe I can get a subquery or smth like that for that.
Please let me know if you have any ideas.
My SQL code
```
SELECT   s.LOC, s.ITEM, 
                sk.OH AS 'Inventory On Hand',
                sc.QTY AS 'On Order',
                s.RECARRIV AS 'Forecast Demand (sales order)'

  FROM [BYIntegration].[SCPOMGR].[UDT_SKUPROJ] s
  LEFT OUTER JOIN SCPOMGR.DMDUNIT d
  ON s.ITEM=D.DMDUNIT

  LEFT OUTER JOIN [BYIntegration].[SCPOMGR].[SKU] sk
  ON  s.[LOC]=sk.[LOC] AND s.[ITEM]=sk.[ITEM] 

  LEFT OUTER JOIN [BYIntegration].[SCPOMGR].[SCHEDRCPTS] sc
  ON s.[LOC]=sc.[LOC] AND s.[ITEM]=sc.[ITEM] 

   WHERE s.LOC LIKE 'DC%' 

  AND s.[ITEM]='9780593189689' AND s.[LOC]='DC01' 

  GROUP BY  s.LOC, s.ITEM, s.RECARRIV, d.DESCR, d.U_PRODUCT_CATEGORY, d.U_MSRP, d.U_FORMAT, d.U_ONSALE_DATE, sk.OH, sc.QTY

  ORDER BY s.LOC, s.ITEM
```

Viewing 3 posts - 1 through 2 (of 2 total)

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

In the situation where Sum([QTY]) returns the correct the number, AVG(OH) or MAX(OH) ought to return only 1 SKU

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

Emperor100 SSCrazy Points: 2173 More actions · Answer 2

As Steve mentioned use AVG() or Min() or Max() aggregate function to return 701.

@jeremyu:

If you want more people to look into, Provide some sample data with table structures with the output you want.

=======================================================================