Sum not working 1

[Cpreston]

Hi

I am trying to change a fairly complicated query by my standards so it sums 2 fields. I have simplified the query by working on 2 know product codes.

What I am getting in the result is 3 rows where I would expect to get only 1 row per code. For the code p022100WW I am expecting the fields to be like this;

M3onWo = 52.02
Useagedayonwo = 59.29

I am getting 3 rows as per the attached screen shot. I know the code is not devised great but if I could get the rows to sum that would be great. Appreciate any help in advance. thanks

SELECT     ROW_NUMBER() OVER (ORDER BY [W/DaysBeforeZeroStock]) AS [Rank], 
			dbo.ProductGroup.Name, dbo.ProductGroup.Level1ID, 
			dbo.[148-vwMinMaxStockLevel_Feed v2 p2].ProductID, 
			dbo.[148-vwMinMaxStockLevel_Feed v2 p2].ProductCode, 
			dbo.[148-vwMinMaxStockLevel_Feed v2 p2].Description, 
			dbo.[148-vwMinMaxStockLevel_Feed v2 p2].StockLevelMin, 
			dbo.[148-vwMinMaxStockLevel_Feed v2 p2].StockLevelMax, 
			CASE WHEN StockAvailable <= 0 THEN 0 ELSE StockAvailable END AS StockAvailable, 
			dbo.[148-vwMinMaxStockLevel_Feed v2 p2].UsagePerDay, 
			CASE WHEN [W/DaysBeforeZeroStock] <= 0 THEN 0 ELSE [W/DaysBeforeZeroStock] END AS [W/DaysBeforeZeroStock], 
			SUM((dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[On WO])) AS M3onWO, 
			dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[Scheduled2],
			SUM (dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[Usage Days on Works Order]) AS Useagedayonwo, 
			dbo.[148-vwMinMaxStockLevel_Feed v2 p2].udfTopTier AS [Top Tier]
			
			
			
FROM         dbo.[148-vwMinMaxStockLevel_Feed v2 p2] INNER JOIN
                      dbo. Product ON dbo.[148-vwMinMaxStockLevel_Feed v2 p2].ProductID = dbo. Product .ProductID INNER JOIN
                      dbo.ProductGroup ON dbo. Product .ProductGroupID = dbo.ProductGroup.ProductGroupID
WHERE     (dbo.ProductGroup.Name NOT LIKE '%mdf%') and (dbo.[148-vwMinMaxStockLevel_Feed v2 p2].ProductCode = 'p022100WW') 
or (dbo.[148-vwMinMaxStockLevel_Feed v2 p2].ProductCode = 'g038125RE45')

GROUP BY 
		 dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[ProductID],
		 dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[ProductCode],
		 dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[On WO],
		 dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[Usage Days on Works Order],
		 dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[W/DaysBeforeZeroStock],
		 dbo.[ProductGroup].[Name],
		 dbo.[ProductGroup].[Level1ID],
		 dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[Description],
		 dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[StockLevelMin],
		 dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[StockLevelMax],
		 dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[StockAvailable],
		 dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[StockAvailable],
		 dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[UsagePerDay],
		 dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[Scheduled2],
		 dbo.[148-vwMinMaxStockLevel_Feed v2 p2].[udfTopTier]

 

[gmmastros]

I'm not surprised that you are getting multiple rows. You see, when you use group by, you will get a row returned for each distinct combination of data.

Looking at the data, I see a StockLevelMax column that has different values in the first 2 rows. There has to be something else that you are grouping on that is also different in the 3rd row. Remember, group by will return a row for each distinct combination of data in all the fields of the group by.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Thanks for the reply.

The stocklevelmax does have 2 different one is for a another product code and the 2 identical ones are the ones I need to sum.
But if I do not put things in the Group by it says it needs to be there .

 

[Cpreston]

Having said that, I have been trying things on the group by and I think I now have it working.
Thanks for pointing me in the right direction. I will try more data. Thanks

 

[Andrzejek]

Your SELECT statement would greatly benefit from using aliases for tables.

Just my opinion... ;-)


---- Andy

There is a great need for a sarcasm font.

 

[Cpreston]

I agree Andrzejek once I have tested it more it will get just that, bit of an emergency job so needed tog et it working ASAP, thanks

 

[Cpreston]

Hi

Thanks to all this is working great now, I have run inot a couple of other issues but will create a new Post for it.