CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2
EZ PZ
EZ PZ

Reputation: 99

Need help optimizing a simple query

Let's say I have the following query:

SELECT
  COUNT(id) numRows, 
  SUM(value) totSum 
FROM someTable
WHERE foo= @bar

Simple enough. It sums all values where the column foo equals to the passed parameter, and counts how many rows were involved in the sum. Now, in my program I have some logic that evaluates the value only if the resultant count is below, let's say, 4.

I want to optimize the query so that I would get an empty result set if count was above 4. My first idea was to slap another condition on the query (where numRows < 4), but that wouldn't work because numRows isn't a column defined in someTable. My second idea was to make the query a subquery, and then filter the results through the subquery's table, like so:

SELECT
 *
 FROM (
   SELECT
     COUNT(id) numRows, 
     SUM(value) totSum 
   FROM someTable
   WHERE foo= @bar
 ) subQuery
where subQuery.numRows < 4

My main question with this solution is whether or not the subquery will be completely computed, or if the main query halts it as soon as numRows hits 4.

Upvotes: 0

Views: 37

Answers (1)

CrimsonKing
CrimsonKing

Reputation: 2896

You can just add this to your original query. Definitely more easy-to-read and probably (not sure) better performance.

HAVING COUNT(ID) < 4

Upvotes: 4

Related Questions