CODEWITHSUNDEEP
google-bigquery
Sandy
Sandy

Reputation: 359

How to replace null with 0 in conditional selection

I got 100 supervisors in my list, and I would like to count how many employees under their supervision at the beginning of 01/01/2018.

These are the codes I tried. However, for supervisors have no employees, their names just disappear in the table. I just wanna keep their names and set the number of employees as 0 if they don't have any. 

select
    Supervisor,
    IFNULL(COUNT(EmpID),0) AS start_headcount
from 
    `T1`
where
     (Last_hire_date < date'2018-01-01'
     AND
    term_date >=  date'2018-01-01' )OR 
    ( Last_hire_date < date'2018-01-01'
     AND
    term_date is null)
group by
    1
order by
    1 asc

The result turned out to be only 92 supervisors appeared in the list who have employees under them. The other 8 supervisors who have no employees just gone. I cannot figure out a better way to improve it.

Can anyone also help with this?

Upvotes: 1

Views: 347

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below is for BigQuery Standard SQL 

#standardSQL
SELECT
  Supervisor,
  COUNTIF(
    (Last_hire_date < DATE '2018-01-01' AND term_date >=  DATE '2018-01-01' ) 
    OR 
    (Last_hire_date < DATE'2018-01-01' AND term_date IS NULL)
  ) AS start_headcount
FROM 
  `T1`
GROUP BY
  1
ORDER BY
  1 ASC

The problem in original query in question was because filtering was happening on WHERE clause level thus effectively totally excluding not matching rows and as result some Supervisor were not shown

So, instead, I moved that condition into COUNTIF() - replacing IFNULL(COUNT()) stuff

In case if your data stored such that you need to take DISTINCT into account - below will address this case

here you are not counting distinct employee ID as the headcount 

#standardSQL
SELECT
  Supervisor,
  COUNT(DISTINCT 
    IF(
      (Last_hire_date < DATE '2018-01-01' AND term_date >=  DATE '2018-01-01' ) 
      OR 
      (Last_hire_date < DATE'2018-01-01' AND term_date IS NULL),
      EmpID,
      NULL
    )
  ) AS start_headcount  
FROM 
  `T1`
GROUP BY
  1
ORDER BY
  1 ASC

Upvotes: 1

Related Questions