COUNT And NULL Values

Kenneth Fisher digs into the COUNT() function and sees how it deals with NULL values:

Count the number of values
1
2
3
SELECT COUNT(FieldName) FROM TableName;
-- or
SELECT COUNT(ALL FieldName) FROM TableName;

The ALL argument is the default and is unnecessary (I didn’t even know it existed until I started this post). Here you are counting the number of non NULL values in FieldName. So in a column with (1, NULL, 1, 2, 3, NULL, 1) you’ll get a count of 5. You do get a nice warning (depending on your ANSI_WARNINGS setting) if there was a NULL value though.

By the way, the ALL operator isn’t useful there, but can be useful along with its counterparts SOME and ANY.  I rarely keep them in my mind, so I instead tend to write EXISTS and NOT EXISTS statements which have operate on an equivalent function.

Related Posts

Refreshing Views After DDL Changes

Eduardo Pivaral shows how you can refresh the metadata for a view in SQL Server after one of its underlying tables or functions changes: So we proceed to execute an alter view over the first view: ALTER VIEW dbo.[vi_invoices_received_by]ASSELECT ConfirmedReceivedBy as [Received by], COUNT(InvoiceID) as [# of Invoices], CustomerIDFROM Sales.InvoicesGROUP BY ConfirmedReceivedBy, CustomerID;GO So we […]

Read More

Finding Gaps in Dates

Jason Brimhall shows how you can find gaps in your data: This method is the much maligned recursive CTE method. In my testing it runs consistently faster with a lower memory grant but does cause a bit more IO to be performed. Some trade-off to be considered there. Both queries are returning the desired data-set […]

Read More

Categories

July 2018
MTWTFSS
« Jun Aug »
 1
2345678
9101112131415
16171819202122
23242526272829
3031