average and crosstab? - 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 2000 Forums

Transact-SQL (2000)

average and crosstab?

Author

Topic

fjorner
Starting Member

5 Posts

Posted - 2004-06-28 : 17:10:32

hey folks, new here

I have a table structure like this:


HeaderID Time Value
1        1    100
1        1    102
1        2    98
1        2    100

I'd like to have a SQL7 view to crosstab and average the data on the Time. My goal is


HeaderID  Time1  Time2
1         101     99

I'm pretty frustrated but it seems simple enough. It didn't seem like that big of a deal when I said it was doable but because you can't do an AVG() inside of a MAX() for the traditional crosstab CASE method, everything seems hosed. Help?

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-06-28 : 17:43:28

Post the code that you have, and I can show you how to do a AVG() of a MAX().

Basically, you need to use a derived table of your crosstab results, and AVG() your MAX()'d field.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

fjorner
Starting Member

5 Posts

Posted - 2004-06-30 : 14:27:51

[code]
select headerid,
MAX(CASE WHEN Time = '1' THEN Avg(VALUE) END) as "TIME1",
MAX(CASE WHEN Time = '2' THEN Avg(VALUE) END) as "TIME2"
..
[/code]

ofcourse, this returns "Server: Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-06-30 : 14:51:08

It's more like this:



SELECT TimeColumn, MAX(TheAverage)
FROM (
SELECT TimeColumn, AVG(Value) AS TheAverage
FROM MyTable
GROUP BY TimeColumn
)

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

fjorner
Starting Member

5 Posts

Posted - 2004-06-30 : 17:58:22

somethings gotta be wrong with that. all I get is

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-30 : 18:21:25

Post the version you just tried.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-06-30 : 18:36:15

You might need to alise the derived table.


SELECT A.TimeColumn, MAX(A.TheAverage)
FROM (
SELECT TimeColumn, AVG(Value) AS TheAverage
FROM MyTable
GROUP BY TimeColumn
)A    -- This line here!

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

fjorner
Starting Member

5 Posts

Posted - 2004-07-01 : 10:16:26

quote:

Originally posted by MichaelP


SELECT A.TimeColumn, MAX(A.TheAverage)
FROM (
SELECT TimeColumn, AVG(Value) AS TheAverage
FROM MyTable
GROUP BY TimeColumn
)A    -- This line here!

bingo. Thanks very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources