Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

  1. items that were created in that day and have zero ItemUnitsCount
  2. 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?

share|improve this question
    
Is it ok to output the result in 1 row? If so, it's easy –  Phil Apr 26 '12 at 13:03
    
@Phil: Nope, that should be separate rows. –  sharptooth Apr 26 '12 at 13:11
    
You have this question tagged for both 2008 R2 and Azure. Is that correct? (Not that it matters for the question, just for categorization.) –  Nick Chammas Apr 26 '12 at 13:12
    
@Nick Chammas: I heard that SQL Azure is a modified version of SQL Server 2k8 R2. Not sure though. –  sharptooth Apr 26 '12 at 13:13
    
Yes, it's the "cloud" version of SQL Server, hosted and managed by Microsoft with major administration differences and some important feature differences. –  Nick Chammas Apr 26 '12 at 13:17
add comment

2 Answers 2

up vote 3 down vote accepted

Group by your condition

SELECT
    ItemCreationDate,
    COUNT(ItemId) AS ComputedCount,
    CASE WHEN ItemUnitsCount = 0 THEN 2 ELSE 1 END AS CountType
FROM
    Items
GROUP BY
    ItemCreationDate,
    CASE WHEN ItemUnitsCount = 0 THEN 2 ELSE 1 END;
share|improve this answer
    
This is the simplest, most direct solution to the OP's problem. –  Nick Chammas Apr 26 '12 at 17:34
add comment
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
share|improve this answer
    
He wants them on separate rows I think... –  JNK Apr 26 '12 at 13:12
    
@JNK A PIVOT should do the trick. –  Nick Chammas Apr 26 '12 at 13:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.