I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Back to Basics: Count, Count, Count, Sum or how to Count

Probably everyone is familiar with the Count(*) function in SQL Server.

But there seems to be a great deal of confusion amongst youngsters (SQL wise) about how all its possible options work.

Let us banish the confusion back to the dark realms where in belongs to:

DECLARE @t TABLE (val INT)
INSERT INTO @t
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 4 UNION ALL
SELECT 4 UNION ALL
SELECT 5

SELECT COUNT(*) AS CountAll, -- counts all rows
       COUNT(val) AS CountAllNoNull, -- counts rows that don't contain NULL
       COUNT(DISTINCT val) AS CountDistinctNoNulls, -- counts the number of distinct values
       COUNT(*) - COUNT(val) AS CountOfNullValues -- count of NULL values in the column 
FROM   @t

 

There are 3 ways in which Count() works:

COUNT(*) - Counts all rows in the table

COUNT(ColumnName) - Counts all rows in the table that don't contain NULL in the specified column

COUNT(DISTINCT ColumnName) - Counts all DISTINCT rows in the table that don't contain NULL in the specified column

We can see that with the use of these 3 options we can get easily get the number of NULL values in the column.

 

So where does the Sum come in here? Sum can be used to get a count of practically anything with just one table scan.

Let's illustrate:

SELECT SUM(CASE WHEN val < 3 THEN 1 ELSE 0 END) AS LessThanTree,
       SUM(CASE WHEN val > 4 THEN 1 ELSE 0 END) AS MoreThanFour
       -- SUM(CASE WHEN <Any condition you can think of> THEN 1 ELSE 0 END) AS Col1
FROM   @t

 

To follow the primary school logic here:

If something fits the condition return 1 else return 0. The sum of all 1's returns the number of items that satisfy the desired condition.

And the condition is limited only by your business requirement.

 

You see there's no need to write nested select statements similar to this:

SELECT (SELECT COUNT(*) FROM MyTable WHERE <someCondition1>) AS cnt1, 
       (SELECT COUNT(*) FROM MyTable WHERE <someCondition1>) AS cnt2

 

Jeff has also has a good post about translating these kind of queries to case statements here.

 

And this might even provide a nice interview question...

 

 

kick it on DotNetKicks.com

Print | posted on Monday, February 04, 2008 5:24 PM | Filed Under [ SQL Server Back to Basics ]

Feedback

Gravatar

# re: Back to Basics: Count, Count, Count, Sum or how to Count

Simple yet so useful. I feel it is important to know these things that looks common and obvious to us but we tend to not realize the usefulness of it. Writing code we tend to just writing query that would work but we may write the same query or may even avoid an extra query by using one or other way of using count as you have written.
2/8/2008 6:11 PM | devnic
Gravatar

# re: Back to Basics: Count, Count, Count, Sum or how to Count

Hello...

I have a problem...hope you can help me.

I have this table:

id table #people
1 1 3
2 1 5
3 4 2
4 3 5
5 1 2

i want to obtain the sum of people sitting per table.

how can I do this?!

Thank you very much! :)

4/3/2009 8:09 PM | almis
Gravatar

# re: Back to Basics: Count, Count, Count, Sum or how to Count

almis:

you can do it like this

select table, sum(people)
from your_table
group by table


that's what group by is for :p
7/7/2009 5:09 PM | Leandro
Gravatar

# re: Back to Basics: Count, Count, Count, Sum or how to Count

nice basic tutor bro ..
btw i've got a question for all guru's in here.

if the table like this :

Status Jobtype Asset Region SumRepair CountWO AssetDecription
Closed Break P-01 2 17.4200000 8 Packing Machine, Dienst 2
Closed Break FM-06.36 2 11.9100000 8 Filling Machine, Piltz 4
Closed Break MB-04-1 2 11.8300000 2 Matter burt TAM 1
Closed Break FM-06.02 2 11.0800000 8 Filling Machine, Jonan 10
Closed Break FM-12.04.01 2 10.1700000 4 Filling Machine, TAM 1
Closed Break CM-01 2 9.6700000 10 EuroPack line Dientz 1 & Dientz 2
Closed Break P-03 2 5.9200000 8 Cartoning Bradman Lake NO.1
Closed Break Sealer 11 2 4.5000000 2 Plastick sealing Machine,Hugo Beck
Closed Break PU.93.10.00 2 4.3000000 1 Pump Unit Hot Water Circulation Acid Tank
Closed Break FM-06.35 2 4.1700000 2 Filling Machine, Piltz 3

and i'd like to get the GrandTotal of SumRepair in the query, how ??
2/19/2010 8:12 AM | ogutsinyo
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET