SQL : NULLIF and COUNT

SQL : NULLIF and COUNT

Posted By: pmietlicki

Published to SQL on Mar 26, 2017

If you'd like to count several elements, the most obvious way would be to use the COUNT function.

But if you want to count all the lines with, also, all the lines where a specific column is not null or does not contain an empty value (or a string of type '"'), how would you do ?

By default, COUNT(*) function does not count empty values and it seems pretty normal. That's where the NULLIF function will be very useful, it permits to return null for elements based on a condition.

Let's take a hypothetical case for a development project manager, you want to do 2 counts:

- Count all the projets

- Count all validated projects (thoses where the progression column is not null)

For each department (group by condition).

Here's how to do it thanks to COUNT and NULLIF functions :

SELECT Etab.Departement AS Departement, COUNT( * ) AS NbProjects, COUNT( NULLIF( Project.progression, '' ) ) AS NbValidatedProject FROM Etab GROUP BY Etab.Departement 

You will retrieve all the projects (COUNT(*)) but also all the projects that have a progession that is not empty (NULLIF will return null for project with an empty progression and COUNT will not count it).

Other possibilities :

- If you have boolean values 0 or 1 you could have done :

SUM(element) then Count(*) - SUM(element), which is faster.

- You can also use SUM(case when progressionCandidature='' then 0 else 1) as NbValidedProject and COUNT(*)

Some resources :

http://www.bennadel.com/blog/579-SQL-COUNT-NULLIF-Is-Totally-Awesome.htm

Tags: NULLIF, SQL

Archive