A quick and easy way to count the percentage of nulls without a where clause in PostgreSQL

This came up the other day, someone wanted to know the percentage of NULL values in a column

Then I said "I bet you I can run that query without using a NULL in the WHERE clause, as a matter of fact, I can run that query without a WHERE clause at all!!"

I created a SQL Server version of this post here: How to count NULLS without using IS NULL in a WHERE clause, this is the PostgreSQL version


To start, first create this table and verify you have 9 rows

CREATE TABLE foo(bar int);
INSERT INTO foo values(1),(null),(2),(3),(4),
 (null),(5),(6),(7);

SELECT * FROM foo;

Here is what the output should look like

To get the NULL values and NON NULL values, you can do something like this

SELECT COUNT(*) as CountAll FROM foo WHERE bar IS NOT NULL;
SELECT COUNT(*) as CountAll FROM foo WHERE bar IS  NULL;

However, there is another way

Did you know that COUNT behaves differently if you use a column name compared to when you use *

Take a look

SELECT COUNT(*) as CountAll, 
  COUNT(bar) as CountColumn
FROM foo;

If you ran that query, the result is the following

CountAll    CountColumn
----------- -----------
9           7

Here is what it looks like in pgAdmin3

Let's see what the PostgreSQL documentation has to say


COUNT(*) number of input rows

COUNT(expression) number of input rows for which the value of expression is not null

This is thus indeed documented behavior, count(*) counts all rows, count(column) skips the rows with NULLS

So now, lets change our query to return the percentage of non null values in the column

Here is what the query looks like

SELECT COUNT(*) as CountAll, 
  COUNT(bar) as CountColumn, 
  (COUNT(bar)/COUNT(*))*100 as PercentageOfNonNullValues 
FROM foo;

Running that gives us this output

CountAll    CountColumn percentageOfNonNullValues
----------- ----------- ---------------------------------------
9           7                  0

Here is what it looks like in pgAdmin3

As you can see, we have a 0 in the percentage column. The reason is that if you do math with integers, you will get an integer back


We can fix this easy by multiplying one of the integers by 1.0, this will convert the integer to a numeric data type . So instead of this line

 (COUNT(bar)/COUNT(*))*100 as PercentageOfNonNullValues 

We will change it to be like this

(COUNT(bar)*1.0/COUNT(*))*100 as PercentageOfNonNullValues

Here is the changed query

SELECT COUNT(*) as CountAll, 
  COUNT(bar) as CountColumn, 
  (COUNT(bar)*1.0/COUNT(*))*100 as PercentageOfNonNullValues 
FROM foo;

Here is the output

CountAll    CountColumn percentageOfNonNullValues
----------- ----------- ---------------------------------------
9           7           77.7777777777800


Here is what it looks like in pgAdmin3

Instead of doing * 1.0, you can also explicitly cast the integer like this

(CAST (COUNT(bar) as numeric(20,10))/COUNT(*))*100 as PercentageOfNonNullValues

The query now looks like this

SELECT COUNT(*) as CountAll, 
  COUNT(bar) as CountColumn, 
  (CAST (COUNT(bar) as numeric(20,10))/COUNT(*))*100 as PercentageOfNonNullValues
FROM foo;

Here is what it looks like in pgAdmin3

That's it for this short post.. hopefully you knew this, if not, then you know it now  :-)

The Vessel, a honeycomb shaped structure that will be a great tourist destination at Hudson Yards in New York City

I took a stroll at the high-line in NYC yesterday and noticed that the Vessel was open to the public


The Vessel is a  copper-colored steel structure comprises 54 interconnecting flights of stairs, 2,500 steps, and 80 landings, it was designed by Heatherwick Studio's. The Vessel rises to a height of 150 ft (45 m) offering views of the city and the Hudson River.

Here is what it looks like




Here it is on the map, it's right at the end/start of the highline and also just a block or so from the Jacob K. Javits Convention Center. I think a lot of visitors to the Jacob Javits Center will stop by

The first thing you will notice is how shiny the Vessel is... in place when the sun hits it..it's like hitting a mirror. I wonder how soon before it turns green because of oxidation. I wouldn't want to be the person who has to polish this copper behemoth




The second thing you need to know is that you can't go inside the Vessel without a ticket. Tickets are free, I picked one up in front of the Vessel near the building right in front of it. I did have to wait 30 minutes to get in, the tickets are only valid for 1 hour

The third thing you will notice once you enter the Vessel is a blue light and a crowd of people near it

You will see something like this

 

Or something like this


 

That spot with the blue light is the selfie station, you put your phone down in the middle, then you stand there while a selfie is taken

I decided not to do that since I don't take selfies, but here is the view from the bottom when you take a normal shot






Here you can see all the stairs, it's nice to walk all over the structure, you get nice views of the city.


 

If for some reason, you can't take the stairs, there is an elevator as well. The thing you see in the middle in the pic below is where the elevator travels

Here is a pic I took of people on the top of the Vessel, it was very windy yesterday, I stayed on the one below the top



I took a picture of these path trains