SQL – WHERE for 2nd SELECT object

I’m trying to find an average of 2 items in my SQL table, an average commission of people who receive a commission from the table, and an average commission, including those who do not get a commission (null).

My code:

SELECT AVG(COMM) AVG_COMM, AVG(COMM) AVEALL
    FROM STAFF
        WHERE COMM IS NOT NULL;

This is what I get:

  AVG_COMM     AVEALL
---------- ----------
      97.5       97.5

This is what I want:

  AVG_COMM     AVEALL
---------- ----------
      97.5       27.851

This is what I want, the AVEALL is an average commission of everyone, including those with a null value.

Thanks.

Answer

You can use COALESCE to convert NULL values to 0 and then take the average to get your AVEALL value:

SELECT AVG(COMM) AVG_COMM, AVG(COALESCE(COMM, 0)) AVEALL
FROM STAFF

Demo on dbfiddle

Advertisement

Answer