Group by query to calculate percentage

Question

I have an PostgreSQL table called my_table that looks like this:

       col_a |                         col_b |
-------------+-------------------------------+
           A | 2025-04-28 16:23:55.961 -0400 |
             | 2024-03-27 17:17:08.100 -0400 |
           C | 2024-03-27 18:57:23.194 -0500 |
           B | 2025-04-28 17:44:51.647 -0500 |
             | 2023-04-28 10:47:30.667 -0400 |

I want to find out for each day in col_b, what percentage of rows have col_a=null

So the result should look like this:

      b_date | percentage |
-------------+------------+
  2023-04-28 |         66 |
  2024-03-27 |         50 |

This is the query I tried, but it is not quite right:

select 
    col_b::date as b_date, 
    (count(col_a=null) * 100)/count(*) as percentage
from my_table
group by b_date
order by b_date asc

Answer 1

You don't need to test if col_a is null or not, because count(expr) only counts the rows where expr is non-null, whereas count(*) counts all rows.

Ref: https://www.postgresql.org/docs/current/functions-aggregate.html

count ( "any" ) → bigint

Computes the number of input rows in which the input value is not null.

Here's a solution:

select 
    col_b::date as b_date, 
    100 - (count(col_a) * 100)/count(*) as percentage
from my_table
group by b_date
order by b_date asc;

Result (tested with PostgreSQL 17.5):

   b_date   | percentage 
------------+------------
 2023-04-28 |          0
 2024-03-27 |         50
 2025-04-28 |        100

Notice your input data has 4-28 on two different years (2023 and 2025), so they count as different dates.

Demo fiddle: https://dbfiddle.uk/ToKqImxS