Taking an average from a few lines

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Taking an average from a few lines

Author

Topic

m4tt
Starting Member

13 Posts

Posted - 2008-03-04 : 04:59:58

I am trying to get an average result from similar records in a view:

Order No: Product: Price:
1 1 5
1 2 3
1 3 7

2 1 5
2 2 3
2 3 7
2 4 2

I need the sql statment to get the average price of order 1 and order 2 seperatly. Giving a result like:

Order No: Av Price:
1 x
2 y

Thanks in advance for you help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 05:02:54

SELECT OrderNo, AVG(Price) FROM Table1 GROUP BY OrderNo

E 12°55'05.25"
N 56°04'39.16"

m4tt
Starting Member

13 Posts

Posted - 2008-03-04 : 05:40:02

Thanks!!!!

raky
Aged Yak Warrior

767 Posts

Posted - 2008-03-04 : 05:47:47

To get closer value to the average. Try this

SELECT ORDERNO,AVG(CAST(PRICE AS FLOAT)) FROM TableName GROUP BY OrderNo

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 06:17:07

Oooohhh.. Cast to float?

DECLARE	@Sample TABLE (OrderNo TINYINT, Product TINYINT, Price TINYINT)

INSERT	@Sample
SELECT	1, 1, 5 UNION ALL
SELECT	1, 2, 3 UNION ALL
SELECT	1, 3, 7 UNION ALL
SELECT	2, 1, 5 UNION ALL
SELECT	2, 2, 3 UNION ALL
SELECT	2, 3, 7 UNION ALL
SELECT	2, 4, 2

SELECT		OrderNo,
		AVG(1.0 * Price)
FROM		@Sample
GROUP BY	OrderNo

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources