snippetsqlMinor

How do I count rows with two properties in one index scan in SQL?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

rowsindexscanwithpropertiessqltwoonehowcount

Problem

I have a table Units where I have rows ItemId, ItemCreationDate, ItemUnitsCount. ItemId is a primary key and I have a clustered index over it.

I need to output the following: for each day I need to count separately

items that were created in that day and have zero ItemUnitsCount

items that were created in that day and have nonzero ItemUnitsCount

and output the count and the date and on any day there can be any number of item of both kinds, maybe none. Each kind of items should be reported separately, so for any day I can have no rows, one row or two rows.

So I crafted the following query:

(SELECT ItemCreationDate, COUNT(ItemId) AS ComputedCount, 1 AS CountType
   FROM Items WHERE ItemUnitsCount<>0 GROUP BY ItemCreationDate )
UNION ALL
(SELECT ItemCreationDate, COUNT(ItemId) AS ComputedCount, 2 AS CountType
   FROM Items WHERE ItemUnitsCount=0 GROUP BY ItemCreationDate )

and this works okay, but when I looks at the actual execution plan I see two clustered index scans one of which has predicate ItemUnitsCount=0 and another has predicate ItemUnitsCount<>0.

ItemUnitsCount can be changed several times over lifetime of the table row, so I'd rather not build an index for it.

Is there a way to build a query with the same effect that results in one index scan instead of two?

Solution

SELECT
    ItemCreationDate
  , SUM(CASE WHEN ItemUnitsCount <> 0 THEN 1 ELSE 0 END) AS CountType1
  , SUM(CASE WHEN ItemUnitsCount = 0 THEN 1 ELSE 0 END) AS CountType2
FROM
    dbo.Items
GROUP BY
    ItemCreationDate

Code Snippets

SELECT
    ItemCreationDate
  , SUM(CASE WHEN ItemUnitsCount <> 0 THEN 1 ELSE 0 END) AS CountType1
  , SUM(CASE WHEN ItemUnitsCount = 0 THEN 1 ELSE 0 END) AS CountType2
FROM
    dbo.Items
GROUP BY
    ItemCreationDate

Context

StackExchange Database Administrators Q#17090, answer score: 4

Revisions (0)

No revisions yet.