# 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

Thursday, February 20, 2020 11:03 PM

• 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.

Best regards,

Sean

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Friday, February 21, 2020 9:18 AM
• 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
```

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Friday, February 21, 2020 2:47 PM

### All replies

• 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.

Best regards,

Sean

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Friday, February 21, 2020 9:18 AM
• 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
```

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Friday, February 21, 2020 2:47 PM
• 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

Friday, February 21, 2020 3:34 PM