Work around average behaviour with nulls I have two tables:


Product (PK product_id, brand, model)
Order (PK invoice_id, customer_name, customer_email, FK product_id)


I am trying to find the product(s) whose number of times that product is sold is closest to the average number of times a product is sold.

The problem is as far as I know the AVG function ignores NULL values when it calculates the average. So if a product has never been sold before, it won't be included in the average, but it should be included for my calculation to be correct. A null should be counted as zero to lower the average.

How can I work around this?
To count NULL like 0 when calculating an average, use

avg(coalesce(colname, 0))


coalesce will return the first of its arguments that is not NULL.