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.

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100.

I tend to prefer

COUNT(CASE WHEN invoice_amount > 100 THEN 1 END)

However, this is just as valid

SUM(CASE WHEN invoice_amount > 100 THEN 1 ELSE 0 END)

I would have thought COUNT is preferable for 2 reasons:

  1. Conveys the intention, which is to COUNT
  2. COUNT probably involves a simple i += 1 operation somewhere, whereas SUM cannot count on its expression to be a simple integer value.

Does anyone have specific facts about the difference on specific RDBMS?

share|improve this question
    
In mysql, you can also use: SUM(invoice_amount > 100) as TRUE is counted as 1. –  ypercube Oct 24 '12 at 21:00
    
@ypercube Yes you can, but is it faster by the same argument? You can also use that in SQLite, and you could also use COUNT(IF(...,1,NULL)) or COUNT(NULLIF(<condition>,0)) but I consider these variations of the same question. –  孔夫子 Oct 24 '12 at 21:06
5  
Can you show us how you benchmark it yourself? –  A-K Oct 24 '12 at 21:19
6  
Another reason to prefer COUNT: the SUM version would return NULL instead of zero on an empty set. Easy to workaround, but why bother. –  Paul White Oct 25 '12 at 2:15
1  
@SQLKiwi That it is. But it scares the children :( –  孔夫子 Oct 25 '12 at 3:43

2 Answers 2

up vote 12 down vote accepted

You mostly answered the question yourself already. I have a few morsels to add:

In PostgreSQL (and probably other RDBMS that support the boolean type) you can use the boolean result of the test directly. Cast it to integer and SUM():

SUM((invoice_amount > 100)::int))

Or use it in a NULLIF() expression and COUNT():

COUNT(NULLIF(invoice_amount > 100, FALSE))

Or with a simple OR NULL:

COUNT((invoice_amount > 100) OR NULL)

Performance is practically identical in my experience. But to verify I ran a quick test with EXPLAIN ANALYZE on a real life table in PostgreSQL 9.1.6.

74208 of 184568 rows qualified with the condition kat_id > 50. All queries return the same result. I ran each like 10 times in turns to exclude caching effects and appended the best result as note:

SELECT SUM((kat_id > 50)::int) FROM log_kat                      -- 438 ms
SELECT COUNT(NULLIF(kat_id > 50, FALSE)) FROM log_kat            -- 437 ms
SELECT COUNT(CASE WHEN kat_id > 50 THEN 1 END) FROM log_kat      -- 437 ms
SELECT count((kat_id > 50) OR NULL) FROM log_kat                 -- 436 ms
SELECT SUM(CASE WHEN kat_id > 50 THEN 1 ELSE 0 END) FROM log_kat -- 432 ms

The CASE expressions seem to be slightly faster. I remember tests where it was the other way round, though. Either way, hardly any real difference in performance.

Faster alternative

If the whole affair is as simple as your test case (most of the time it isn't), you can just rewrite to:

SELECT count(*) FROM log_kat WHERE kat_id > 50;                  -- 202 ms (!)

Which is the real king of performance and can also easily utilize an index.

share|improve this answer
    
The last rewrite is a given, but the context would have been queries that take both COUNT(column), SUM(CASE.. or some such. Thanks for the insight on PostgreSQL, and the hint on casting bool to int. –  孔夫子 Oct 25 '12 at 1:57
1  
One more way that can be tested: COUNT(CASE WHEN invoice_amount > 100 THEN invoice_amount END) –  ypercube May 2 '13 at 15:42
    
@ypercube: Performs almost as fast as COUNT(CASE WHEN invoice_amount > 100 THEN 1 END). Using the column invoice_amount in place of the the constant 1 this would be as expected. Another variant, omitting the ELSE from CASE: SUM(CASE WHEN invoice_amount > 100 THEN 1 END). The default NULL doesn't add to the count. Very slightly slower than explicit ELSE in my tests. –  Erwin Brandstetter May 2 '13 at 16:27

This is my test on SQL Server 2012 RTM.

if object_id('tempdb..#temp1') is not null drop table #temp1;
if object_id('tempdb..#timer') is not null drop table #timer;
if object_id('tempdb..#bigtimer') is not null drop table #bigtimer;
GO

select a.*
into #temp1
from master..spt_values a
join master..spt_values b on b.type='p' and b.number < 1000;

alter table #temp1 add id int identity(10,20) primary key clustered;

create table #timer (
    id int identity primary key,
    which bit not null,
    started datetime2 not null,
    completed datetime2 not null,
);
create table #bigtimer (
    id int identity primary key,
    which bit not null,
    started datetime2 not null,
    completed datetime2 not null,
);
GO

--set ansi_warnings on;
set nocount on;
dbcc dropcleanbuffers with NO_INFOMSGS;
dbcc freeproccache with NO_INFOMSGS;
declare @bigstart datetime2;
declare @start datetime2, @dump bigint, @counter int;

set @bigstart = sysdatetime();
set @counter = 1;
while @counter <= 100
begin
    set @start = sysdatetime();
    select @dump = count(case when number < 100 then 1 end) from #temp1;
    insert #timer values (0, @start, sysdatetime());
    set @counter += 1;
end;
insert #bigtimer values (0, @bigstart, sysdatetime());
set nocount off;
GO

set nocount on;
dbcc dropcleanbuffers with NO_INFOMSGS;
dbcc freeproccache with NO_INFOMSGS;
declare @bigstart datetime2;
declare @start datetime2, @dump bigint, @counter int;

set @bigstart = sysdatetime();
set @counter = 1;
while @counter <= 100
begin
    set @start = sysdatetime();
    select @dump = SUM(case when number < 100 then 1 else 0 end) from #temp1;
    insert #timer values (1, @start, sysdatetime());
    set @counter += 1;
end;
insert #bigtimer values (1, @bigstart, sysdatetime());
set nocount off;
GO

Looking at individual runs and batches separately

select which, min(datediff(mcs, started, completed)), max(datediff(mcs, started, completed)),
            avg(datediff(mcs, started, completed))
from #timer group by which
select which, min(datediff(mcs, started, completed)), max(datediff(mcs, started, completed)),
            avg(datediff(mcs, started, completed))
from #bigtimer group by which

The results after running a 5 times (and repeating) is quite inconclusive.

which                                       ** Individual
----- ----------- ----------- -----------
0     93600       187201      103927
1     93600       187201      103864

which                                       ** Batch
----- ----------- ----------- -----------
0     10108817    10545619    10398978
1     10327219    10498818    10386498

It shows that there is far more variability in the running conditions than there is difference between the implementation, when measured with the granularity of the SQL Server timer. Either version can come on top, and the maximum variance I have ever got is 2.5%.

However, taking a different approach:

set showplan_text on;
GO
select SUM(case when number < 100 then 1 else 0 end) from #temp1;
select count(case when number < 100 then 1 end) from #temp1;

StmtText (SUM)

  |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012] END))
       |--Stream Aggregate(DEFINE:([Expr1011]=Count(*), [Expr1012]=SUM([Expr1004])))
            |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [tempdb].[dbo].[#temp1].[number]<(100) THEN (1) ELSE (0) END))
                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#temp1]))

StmtText (COUNT)

  |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1008],0)))
       |--Stream Aggregate(DEFINE:([Expr1008]=COUNT([Expr1004])))
            |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [tempdb].[dbo].[#temp1].[number]<(100) THEN (1) ELSE NULL END))
                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#temp1]))

From my reading, it would appear that the SUM version does a little more. It is performing a COUNT in addition to a SUM. Having said that, COUNT(*) is different and should be faster than COUNT([Expr1004]) (skip NULLs, more logic). A reasonable optimizer will realise that [Expr1004] in SUM([Expr1004]) in the SUM version is an "int" type and so utilise an integer register.

In any case, while I still believe the COUNT version will be faster in most RDBMS, my conclusion from testing is that I am going to go with SUM(.. 1.. 0..) in the future, at least for SQL Server for no other reason than the ANSI WARNINGS being raised when using COUNT.

share|improve this answer
    
+1 for answering your own question with proper due diligence. –  RolandoMySQLDBA May 2 '13 at 15:17

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.