sum of field with joins

[tonyvee1973]

Hi Guys
Having a bit of an issue with a sql query hope someone can help with?
Need to get the sum of "STK_PHYSICAL on line 3 in the script below but my syntax is lame - can someone help please?

Thanks as always





SELECT DISTINCT dbo.STK_STOCK.STKCODE, '' AS Spare, dbo.STK_STOCK.STKNAME, '' AS UnitOfMeasure, '' AS UnitOfSales, dbo.STK_STOCK.STK_BASEPRICE,
dbo.STK_STOCK3.STK_USRNUM1 AS RRP, '' AS PriceCode,
sum (dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL,
dbo.STK_STOCK.STK_ORDER_IN, '' AS NextDElivery, '' AS ColourCode, '' AS ProductGroup, '' AS Discontinued,
(CASE dbo.STK_STOCK3.STK_USRFLAG1 WHEN 1 then 'Y' ELSE 'N' END) as Dead,
dbo.STK_STOCK.STK_BARCODE AS BarCode, '' AS MinimumMultiples, '' AS GenericCode5,
'' AS GenericCode6, dbo.STK_STOCK.STK_SORT_KEY, dbo.STK_STOCK.STK_SORT_KEY1, '' AS FreeType3, '' AS FreeType4, '' AS FreeType5, '' AS FreeType6, '' AS FreeType7,
'' AS FreeType8, '' AS BreakCode, dbo.STK_STOCK.STK_COSTPRICE / dbo.STK_STOCK_2.STK_BUY_UNIT1 AS STK_COSTPRICE, '' AS MinimumPrice,
'' AS RangeCode, '' AS MinimumPromotional, '' AS TaxCode, '' AS MinimumOrderQuantity, '' AS FutureStock, dbo.STK_LOCATION.LOC_CODE AS LOCATION, dbo.STK_LOCATION.LOC_PHYSICAL, dbo.STK_LOCATION.LOC_QTYPRINTED, dbo.STK_LOCATION.LOC_RESERVE_OUT
FROM dbo.STK_STOCK INNER JOIN
dbo.STK_STOCK_2 ON dbo.STK_STOCK.STKCODE = dbo.STK_STOCK_2.STKCODE2 INNER JOIN
dbo.STK_STOCK3 ON dbo.STK_STOCK_2.STKCODE2 = dbo.STK_STOCK3.STKCODE3 INNER JOIN
dbo.STK_LOCATION ON STK_STOCK3.STKCODE3 = dbo.STK_LOCATION.LOC_STOCK_CODE
WHERE (dbo.STK_STOCK3.STK_USRFLAG1 <> 1) AND (dbo.STK_LOCATION.LOC_CODE) NOT IN ('CREDIT','RETURN','REJECT')

 

[IanWaterman]

You need to group query

Decide which fields are your group fields, the rest you will need to find Max() or MIN()

eg
Max(CASE dbo.STK_STOCK3.STK_USRFLAG1 WHEN 1 then 'Y' ELSE 'N' END) as Dead,

After your Where clause

Group By dbo.STK_STOCK.STKCODE, dbo.STK_STOCK.STKNAME....etc

If you want to group by Dead you must use full expression in group by clause

Group By dbo.STK_STOCK.STKCODE, dbo.STK_STOCK.STKNAME, (CASE dbo.STK_STOCK3.STK_USRFLAG1 WHEN 1 then 'Y' ELSE 'N' END), ....

Ian

 

[tonyvee1973]

Hi Ian
Thanks for your response but i'm slightly confused.
The script gives me the results i require. I have multiple line results for "dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT" which i need added together and exported as AS STK_PHYSICAL" hence the line i tried putting together which is: sum (dbo.STK_LOCATION.LOC_PHYSICAL - dbo.STK_LOCATION.LOC_RESERVE_OUT) AS STK_PHYSICAL above
.
Excuse my ignorance but dont really understand the grouping side

 

[IanWaterman]

What do you want to sum the amount for.

You can only do it for the whole query or for a particular field I would guess you want it summed by dbo.STK_STOCK.STKCODE

So you then have to group it by dbo.STK_STOCK.STKCODE. However, you would then need to aggregate all the other fields too.

Please show sample set of data as it stands now and also what you expect to see instead

Ian