Better way of Writing this Query

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)

Better way of Writing this Query

Author

Topic

chava_sree
Yak Posting Veteran

56 Posts

Posted - 2008-03-24 : 20:14:47

Guys,

is there any better way of writing the below query. i don't want to use select again and again.. and as my table has millions of records.. can u pls.suggest me the best possible method.

Select comm_Name,comm_CreationDate,
Total_Users = (Select count(*) from User_Community where UC_COMMID = comm_ID),
New_Members = (Select count(*) from User_Community where convert(char(10),uc_timestamp,101) > CONVERT (char (10) ,'01/01/2008' ,101) and UC_CommID = COMM_ID)
from Community

thanks

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2008-03-24 : 20:48:19

something like

SELECT A.comm_Name, A.comm_CreationDate, B.TotalUsers, B.New_Members
FROM Community A
INNER JOIN
(
SELECT Z.UC_COMMID, Total_Users = COUNT(*), New_Members = COUNT(CASE WHEN Z.uc_timestamp >= '20080101' THEN 1 END)
FROM User_Community Z
GROUP BY Z.UC_COMMID
) B ON B.UC_COMMID = A.comm_ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources