combining selects into a single record

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)

combining selects into a single record

Author

Topic

akashenk
Posting Yak Master

111 Posts

Posted - 2009-12-17 : 14:44:59

I have a table which contains numeric data, and I would like to retrieve various statistics for this table. I have a number of SELECT statements, and each returns a different statistic.

SELECT COUNT(columnA WHERE columnD='somedate')

SELECT AVG(columnB) Where columnA= 'somevalue' AND columnD='somedate' AND columnB > 0

SELECT AVG(columnC) Where columnA= 'somevalue' AND columnD='somedate'AND columnC > 0

etc., etc.

Is there a way to combine these Select statements, so that instead of returning a record for each select, I return a single result with a named column for each statistic?

So, my single returned record might look like...

COUNT, AVGA, AVGB
14, 4.5, 7.25

instead of three separate results for the three select statements.

Thanks in advance!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-17 : 15:35:14

something like this


 SELECT [Count] = SUM(CASE WHEN columnd = 'somedate' THEN 1 ELSE 0 END
     ,[AVGB] = AVG(CASE WHEN columnA = 'somevalue' and columnD='somedate' AND columnB > 0
                           THEN columnB else null 
                        END)

     ,[AVGC] = AVG(CASE WHEN columnA = 'somevalue' and columnD='somedate' AND columnC > 0
                           THEN columnC  
                        END)

Everyday I learn something that somebody else already knew

akashenk
Posting Yak Master

111 Posts

Posted - 2009-12-17 : 20:39:05

thanks... that did the trick!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources