Show all and Calculate AVG by SKU

Question

User-1641868886 posted

I'm trying to ge to a table that will show all rows, then show average price by individual SKU, like:

SKU	PRICE	SKUAVG	SKUDIFF	ABOVE/BELOW
AAA	12.00	15.00	-3.00	Below
AAA	18.00	15.00	+3.00	Above
AAA	15.00	15.00	0.00	At Avg
BBB	6.00	9.00	-3.00	Below
BBB	9.00	9.00	0.00	At Avg
BBB	12.00	9.00	+3.00	Above

...with the thought that I may drop the "SKU AVG" column from the production table as it is redundant. 

Here is my attempt to create sql to arrive at this...tried several iterations, and looked around on many sql server sites at various ways to do AVG, DISTINCT, etc.

declare @SKUAVG numeric(18,2)
declare @SKUDIFF numeric(18,2)
SELECT @SKUAVG=AVG(PRICE)

FROM GROCBills
GROUP BY SKU

SELECT DISTINCT [SKU], Price,
Price - @SKUAVG as [@SKUDIFF],
CASE
	WHEN Price > @SKUAVG THEN 'ABOVE'
	WHEN Price < @SKUAVG THEN 'Below'
	ELSE 'At Avg'
	End as 'ABOVE/BELOW'

FROM GrocBills
ORDER BY SKU

...what the resulting output is doing is computing an "SKUDIFF" not by the AVG of each SKU, but-oddly-by the last SKU in the table, so each "SKUDIFF" is calculated on the AVG of that last SKU.

If anyone can help me with this statement, or point me to an example of a similar sql problem, thanks for any help.

RC

Answer 1

User-1330468790 posted

Hi, ReidMelSam，

I think you are trying to find out why the result is different from you expected. 

The error exists in the "SELECT @local_variable" statement.

 

As document illustrated : 

'SELECT @local_variable is typically used to return a single value into the variable. However, when expression is the name of a column, it can return multiple values. If the SELECT statement returns more than one value, the variable is assigned the last value that is returned.

 

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.'

 

Therefore, the select @ statement only assigns the @SKUAVG with single value which is the last returned value. You may need to find another way to achieve your target and I suggest using nested select. Temp table also works.

Code:

SELECT DISTINCT a.[SKU], Price,

Price - b.SKUAVG as [SKUDIFF],
CASE
WHEN Price > b.SKUAVG THEN 'ABOVE'
WHEN Price < b.SKUAVG THEN 'Below'
ELSE 'At Avg'
End as 'ABOVE/BELOW'

FROM GrocBills as a 
Left Join (SELECT AVG(PRICE) AS SKUAVG, SKU
FROM GROCBills
GROUP BY SKU) AS b on a.SKU = b.SKU

ORDER BY a.SKU

Data in Database:

Result Data:

If you declare @SKUDIFF for other purposes, please tell me and we could find alternative way.

Hope this can help you.

Best regards,

Sean

Answer 2

User77042963 posted

CREATE TABLE GrocBills(
   SKU     VARCHAR(3) NOT NULL 
  ,PRICE   INTEGER  NOT NULL
  ,SKUAVG  INTEGER  NOT NULL
  ,SKUDIFF INTEGER  NOT NULL
);
INSERT INTO GrocBills(SKU,PRICE,SKUAVG,SKUDIFF) VALUES
 ('AAA',12,15,-3)
,('AAA',18,15,3)
,('AAA',15,15,0)
,('BBB',6,9,-3)
,('BBB',9,9,0)
,('BBB',12,9,3);
 

SELECT   [SKU], Price,
Price - AVG(PRICE)Over(Partition by SKU) as [SKUDIFF],
CASE
	WHEN Price > AVG(PRICE)Over(Partition by SKU) THEN 'ABOVE'
	WHEN Price < AVG(PRICE)Over(Partition by SKU) THEN 'Below'
	ELSE 'At Avg'
	End as 'ABOVE/BELOW'

FROM GrocBills
ORDER BY SKU

drop TABLE GrocBills

Answer 3

User-1641868886 posted

Thanks much to both responders. Both answers worked exactly as needed. I'll mark both as "Answer." I do think the "Partition by SKU" is a more elegant method and taught me a new operator (Partition by) as I had not previously used it.

I need to study some material-not just search the forums and tutorial sites-for some in-depth knowledge on these types of computed columns with AVG, etc. I would appreciate any suggestion if there's a good up-to-date book or other resource.

Thanks again both!

RC