Aggregate functions question - 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

SQL Server Development (2000)

Aggregate functions question

Author

Topic

morleyz
Starting Member

17 Posts

Posted - 2008-09-22 : 16:04:44

I'm not even sure if this is possible, so it's OK to tell me I'm screwed, but I was wondering if there's a way to aggregate data for some fields and just duplicate that data for others (such as text fields).

Example Data:
Row1, Row2, Row3, Row4
1, 15, 12, Bob's a man
1, 12, 3, Mary's a woman
1, 3, 5, Joe's a dog
2, 3, 7, Jack's a man
2, 9, 18, Melissa's a cat

So I'd like to do something like:
SELECT avg(row2), avg(row3), Row4 FROM TABLE GROUP BY Row1

and get results like:
10, 6.67, Bob's a man
10, 6.67, Mary's a woman
10, 6.67, Joe's a dog
6, 12.5, Jack's a man
6, 12.5, Melissa's a cat

Is there a way to do something like this with a single query? The GROUP BY Clause generates an error and I agree that the SQL isn't correct, but I didn't know if there was a correct way to do this.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-22 : 16:14:50

You would have to do something like this to get those results. BYW, I think you are confusing Rows with Columns


select d.row1
       ,d.avgRow2
       ,d.avgRow3
       ,t.row4
from   (
       SELECT row1, avg(row2) avgrow2, avg(row3) avgrow3 FROM [TABLE] GROUP BY Row1
       ) d
join   [Table] t
              on t.row1 = d.row1

Be One with the Optimizer
TG

morleyz
Starting Member

17 Posts

Posted - 2008-09-22 : 16:22:55

Yes...I should have used Col1, Col2, etc...I had them right in my head, just chose the wording poorly. Let me try that query in the real world and I'll see if that's what I needed.

Thanks!

morleyz
Starting Member

17 Posts

Posted - 2008-09-23 : 10:37:44

This works as I need. Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources