How to calculate the sum of averages with sql quer

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

Development Tools

ASP.NET

How to calculate the sum of averages with sql quer

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2008-01-15 : 05:29:19

How to calculate the sum of averages with sql query?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-15 : 05:40:01

SELECT SUM(avgCol2) FROM (
SELECT Col1, AVG(Col2) AS avgCol2 FROM Table1 GROUP BY Col1
) AS d

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

Swati Jain
Posting Yak Master

139 Posts

Posted - 2008-01-15 : 08:00:39

quote:
Originally posted by Peso

SELECT SUM(avgCol2) FROM (
SELECT Col1, AVG(Col2) AS avgCol2 FROM Table1 GROUP BY Col1
) AS d

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

Hi Peso,

This query is working brilliently but i have to resolve one more problem.
I want to filter this with
some columns in other tables like Table2, table3 etc. It is also having common id to join
but how to set filter criteria With above query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-15 : 08:12:18

[code]SELECT SUM(d.avgCol2)
FROM (
SELECT AVG(t1.Col2) AS avgCol2
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.ColX = t1.ColBlue
WHERE t1.SomeCol = 'New'
AND t2.ThirdCol = 'Oops'
GROUP BY t1.Col1,
t2.ColY
) AS d[/code]

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

Swati Jain
Posting Yak Master

139 Posts

Posted - 2008-01-16 : 05:13:02

quote:

Originally posted by Peso

SELECT	SUM(d.avgCol2)
FROM	(
		SELECT		AVG(t1.Col2) AS avgCol2
		FROM		Table1 AS t1
		INNER JOIN	Table2 AS t2 ON t2.ColX = t1.ColBlue
		WHERE		t1.SomeCol = 'New'
				AND t2.ThirdCol = 'Oops'
		GROUP BY	t1.Col1,
				t2.ColY
	) AS d

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

Hi Peso. This is quite fine . but the where clause is generated dynamicaly with asp.net code. It is appended to outside of the query.
but with this query it gets appended outside the query.which dnt give the right result.
Is there any workaround for this?
where clause is appended as follows
SELECT SUM(d.avgCol2)
FROM (
SELECT AVG(t1.Col2) AS avgCol2
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.ColX = t1.ColBlue
GROUP BY t1.Col1,
t2.ColY
) AS d
where AND t2.ThirdCol = 'Oops'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-16 : 05:22:47

Change the ASP.Net code?

Really, do you expect me to know enough about your internal ASP.Net code to make any assumption how to do work arounds?

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-16 : 05:28:48

<< but the where clause is generated dynamicaly with asp.net code.>>

Can you post the full code you used?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources