Medium Of Sum - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Medium Of Sum

Author

Topic

LacOniC
Starting Member

29 Posts

Posted - 2009-01-08 : 02:54:15

Query Results:

ColumnA ColumnB
A 2
A 1
A 2
A 3
A 3
A 1
B 2
B 2
B 5

I want to group by ColumnA and medium of sum of ColumnA at ColumnB like:

A 2 --> (2+1+2+3+3+1) / 6
B 3 --> (2+2+5) / 3

Any help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 03:01:40

[code]SELECT ColumnA,
AVG(1.0E * ColumnB)
FROM Table1
GROUP BY ColumnA
ORDER BY ColumnA[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 03:02:55

[code]SELECT ColumnA, AVG(ColumnB*1.0)
FROM Table1
GROUP BY ColumnA
ORDER BY ColumnA
[/code]

if columnB is int and you want decimal results

LacOniC
Starting Member

29 Posts

Posted - 2009-01-08 : 03:13:02

My ColumnB is a subquery. So i'm getting an error like:

"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 03:24:16

[code]SELECT ColumnA,
AVG(1.0E * ColumnB)
FROM (
SELECT ColumnA, ColumnB FROM <your query here>
) AS d
GROUP BY ColumnA
ORDER BY ColumnA[/code]

E 12°55'05.63"
N 56°04'39.26"

LacOniC
Starting Member

29 Posts

Posted - 2009-01-08 : 04:14:33

What is syntax? I try like that doesn't work:

SELECT ColumnA, ColumnB FROM <your query here>

SELECT ColumnA, ColumnB FROM (SELECT * FROM TableA)

Incorrect syntax near the keyword 'select'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 04:20:36

SELECT ColumnA, ColumnB FROM ( <your query here> ) as f

SELECT ColumnA, ColumnB FROM (SELECT * FROM TableA)AS d

E 12°55'05.63"
N 56°04'39.26"

LacOniC
Starting Member

29 Posts

Posted - 2009-01-08 : 04:22:46

quote:

Originally posted by Peso

SELECT   ColumnA,
         AVG(1.0E * ColumnB)
FROM     (
	     SELECT ColumnA, ColumnB FROM <your query here>
	 ) AS d
GROUP BY ColumnA
ORDER BY ColumnA

E 12°55'05.63"
N 56°04'39.26"

It's ok. Sorry. Thank you very much.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-01-08 : 09:10:13

The term is "median", not "medium"...
One is for Stats. The other is for Steaks.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources