SQL Question - Remove Sub Selects? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

SQL Question - Remove Sub Selects?

Author

Topic

dennisgaudenzi
Starting Member

26 Posts

Posted - 2013-11-23 : 09:42:25

Good morning.

I have the following sample data (T_MyTable):


RowID   ColName   Flag1   Flag2
-------------------------------
1       Data A    0       1
2       Data B    0       1
3       Data C    1       1
4       Data D    1       0

I am selecting data from a bunch of tables, but need to really only display the data from the sample table data above.

Right now, the only way I can do what I want is with the following SQL:


SELECT ColName,
  (SELECT COUNT(RowID) FROM T_MyTable WHERE Flag1 = 0) AS Flag1Count,
  (SELECT COUNT(RowID) FROM T_MyTable WHERE Flag2 = 0) AS Flag2Count
FROM T_MyTable

My question is if there is a way to get rid of the sub selects to return the count data another way. I originally thought of using the OVER PARTITION BY clause, but that will not work since I have the WHERE clause on the Flag1 and Flag2 fields to get the counts of each.

I am just trying to make this as clean with as little look ups as possible. I will have a few hundred thousand to over a million rows, so the SQL above is going to slow over time.

Thanks in advance for the help. Please let me know if I missed anything to help answer.

Dennis

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-23 : 10:08:10

you can use OVER PARTITION BY itself like this


SELECT ColName,
COUNT(CASE WHEN Flag1 = 0 THEN RowID ELSE NULL END) OVER () AS Flag1Count,
COUNT(CASE WHEN Flag2 = 0 THEN RowID ELSE NULL END) OVER () AS Flag2Count
FROM T_MyTable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

dennisgaudenzi
Starting Member

26 Posts

Posted - 2013-11-23 : 12:54:44

This is great - did not even think about doing it that way. Thanks for the tip!! Dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources