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...