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:
x
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.
Advertisement
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