We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

# Show all and Calculate AVG by SKU[Answered] RSS

## 3 replies

Last post Feb 21, 2020 03:34 PM by ReidMelSam

• ReidMelSam

Member

39 Points

406 Posts

### Show all and Calculate AVG by SKU

Feb 20, 2020 11:03 PM|ReidMelSam|LINK

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

"Look at it go, Homer; this one's gonna go for miles!"
• Sean Fang

Contributor

3060 Points

902 Posts

### Re: Show all and Calculate AVG by SKU

Feb 21, 2020 09:18 AM|Sean Fang|LINK

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.

<div> </div>

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.

<div>Code:</div>
```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

ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
• limno

All-Star

123252 Points

10024 Posts

Moderator

### Re: Show all and Calculate AVG by SKU

Feb 21, 2020 02:47 PM|limno|LINK

```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
```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• ReidMelSam

Member

39 Points

406 Posts

### Re: Show all and Calculate AVG by SKU

Feb 21, 2020 03:34 PM|ReidMelSam|LINK

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

"Look at it go, Homer; this one's gonna go for miles!"