select count of rows based on condition

Question

Post reply

select count of rows based on condition

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110588 More actions · Answer 1

ChrisM@Work - Thursday, October 5, 2017 6:45 AM

sgmunson - Thursday, October 5, 2017 6:18 AM

ScottPletcher - Wednesday, October 4, 2017 2:06 PM

bartedgerton - Wednesday, October 4, 2017 1:33 PM

I prefer to use COUNT because I think it is more readable. I usually use in the 2012 compatibility, which seems to be the easiest to understand/most readable, to me, anyway:

select
COUNT(IIF(TYPE ='D',1,NULL)) AS 'TotalXRows',
COUNT(IIF(TYPE ='L' ,1,NULL)) AS 'TotalYRows',
COUNT(1) AS 'TotalRows'
from dbo.Table (nolock)

If you do use SUM, I would recommend to use zero instead of NULL as SUM seems to run faster when there are no nulls in the data.

IIF should never be allowed in SQL. It's too foreign an approach, and thus much more difficult to understand if you're not a developer in a language that uses it. And SQL shouldn't be corrupted just to "simplify" things for developers. CASE is straightforward enough even for non-developers, IIF isn't.

Scott ... just curious if you know of anyone testing performance on whether a CASE expression would perform any differently than a functionally equivalent IIF, or if you know what SQL Server does with IIF internally. I suspect it may turn it into the equivalent CASE expression, but unfortunately, it just seems that every time I come across something like this, it's when I have the least possible time available to test it out and see. Maybe Jeff Moden can apply one of his test rigs to this one.... Jeff?

If you look at Node 3 of the execution plan for this statement

SELECT

COUNT(IIF(System_Type_Id =127,1,NULL)) AS 'TotalXRows'

FROM SYS.COLUMNS
it's a compute scalar, with this expression:
[Expr1018] = Scalar Operator(CASE WHEN [MyDB].[sys].[syscolpars].[xtype]=(127) THEN (1) ELSE NULL END)

EDIT: JIC version is significant, this is with 2012.

Yeah, just as I suspected... a CASE expression under the covers. Thanks for looking into it.

Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)

bartedgerton SSCrazy Points: 2062 More actions · Answer 2

I've already completed dozens of other tasks and you guys are still talking about this issue. Lol.

Lucky for me, I'm dealing with hundreds of transactions per minute, not millions, so quibbling over a few clock cycles is less important for my needs. Of course, if I had any kind of performance concerns at all, I'd be extremely careful about doing an aggregation like this at all. Depending on the situation, there might be better alternatives.