Average price calculation

Question

Post reply

Average price calculation

Puiu

SSC Enthusiast

Points: 184

December 4, 2023 at 1:43 pm

#4339516

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]
GO

INSERT 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!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

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_1
group by idProduct, Product

Puiu SSC Enthusiast Points: 184 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

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.

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

Puiu SSC Enthusiast Points: 184 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5