# Average price calculation

• Hello!

I need to make a query that will calculate my average price for each item.

`CREATE TABLE [dbo].[Table_1]([id] [int] IDENTITY(1,1) NOT NULL,[idProduct] [int] NOT NULL,[Product] [nvarchar](50) NOT NULL,[Quantity] [decimal](18, 2) NOT NULL,[Price] [decimal](18, 2) NOT NULL, CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ([id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO [dbo].[Table_1] ([idProduct],[Product],[Quantity],[Price])  VALUES (1,'Prod1',100,5)INSERT INTO [dbo].[Table_1] ([idProduct],[Product],[Quantity],[Price])  VALUES (1,'Prod1',50,7)INSERT INTO [dbo].[Table_1] ([idProduct],[Product],[Quantity],[Price])  VALUES (2,'Prod2',20,15)`

average price calculation: (100*5 + 50*7) / 150

Expected Result:

1,'Prod1',150,6.07

2,'Prod2',20,15

Thank you!

• Your expected result is off.

The value shouldn't be 6.07 it should be 5.666666666 recurring

100*5 = 500

50*7 = 350

850/150 = 5.6666666666666666666666666

`select idproduct, product, sum(quantity*price)/sum(quantity)from Table_1group by idProduct, Product`
• yes, I made a mistake when I copied the value. It's exactly what I need, thank you!

• Puiu wrote:

yes, I made a mistake when I copied the value. It's exactly what I need, thank you!

The next question is, do you now understand how to do such a thing for other problems in the future?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• I have learned something, so I hope that I will do better in the future.

• Puiu wrote:

I have learned something, so I hope that I will do better in the future.

Awesome.  Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.