nested query i think - 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

General SQL Server Forums

New to SQL Server Programming

nested query i think

Author

Topic

pmccann1
Posting Yak Master

107 Posts

Posted - 2009-01-15 : 11:02:52

ID HOURS Status
1 170 20
2 180 21
2 35 21
3 100 20
3 20 21

how would i go about basically suming the hours for a given id where the staus 20 exists for instance form the above sample my result would look like

ID Sum(hours)
1 170
3 120

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-15 : 11:15:36

SELECT [ID]
,SUM([Hours]) as h
FROM TableName
Where ID IN (Select ID FROM TableName WHERE [status]=20)
Group by ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 23:43:40

[code]
SELECT [ID]
,SUM([Hours]) as h
FROM TableName
Group by ID
HAVING SUM(CASE WHEN Status=20 THEN 1 ELSE 0 END)>0
[/code]

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-16 : 04:47:10

Visakh's method is faster. I tested on table with 100,000 rows with none of the query columns indexed (different identity column had clustered). Here's results:
Using "Where ID IN ":
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 69 ms.

Using "HAVING":
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 59 ms.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-16 : 05:15:53

IN most probably also creates a worktable in tempdbm thus having some writes too.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 08:54:38

quote:
Originally posted by darkdusky

Visakh's method is faster. I tested on table with 100,000 rows with none of the query columns indexed (different identity column had clustered). Here's results:
Using "Where ID IN ":
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 69 ms.

Using "HAVING":
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 59 ms.

Thanks for the comparison test

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources