Postgresql returns 0 if the return value is null

Question

I have a query that returns avg (price)

select avg(price) from( select *, cume_dist() OVER (ORDER BY price desc) from web_price_scan where listing_Type='AARM' and u_kbalikepartnumbers_id = 1000307 and (EXTRACT(Day FROM (Now()-dateEnded)))*24 < 48 and price>( select avg(price)* 0.50 from(select *, cume_dist() OVER (ORDER BY price desc) from web_price_scan where listing_Type='AARM' and u_kbalikepartnumbers_id = 1000307 and (EXTRACT(Day FROM (Now()-dateEnded)))*24 < 48 )g where cume_dist < 0.50 ) and price<( select avg(price)*2 from( select *, cume_dist() OVER (ORDER BY price desc) from web_price_scan where listing_Type='AARM' and u_kbalikepartnumbers_id = 1000307 and (EXTRACT(Day FROM (Now()-dateEnded)))*24 < 48 )d where cume_dist < 0.50) )s having count(*) > 5 

how to make it return 0 if value is not available?

Answer 1

use union

 COALESCE(value [, ...]) 

 The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display. 

Edit

Here is a COALESCE example with your request:

 SELECT AVG( price ) FROM( SELECT *, cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan WHERE listing_Type = 'AARM' AND u_kbalikepartnumbers_id = 1000307 AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48 AND COALESCE( price, 0 ) > ( SELECT AVG( COALESCE( price, 0 ) )* 0.50 FROM ( SELECT *, cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan WHERE listing_Type='AARM' AND u_kbalikepartnumbers_id = 1000307 AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48 ) g WHERE cume_dist < 0.50 ) AND COALESCE( price, 0 ) < ( SELECT AVG( COALESCE( price, 0 ) ) *2 FROM( SELECT *, cume_dist() OVER ( ORDER BY price desc ) FROM web_price_scan WHERE listing_Type='AARM' AND u_kbalikepartnumbers_id = 1000307 AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48 ) d WHERE cume_dist < 0.50) )s HAVING COUNT(*) > 5 

IMHO COALESCE should not be used with AVG because it changes the value. NULL means the unknown and nothing else. This is not like using in SUM . In this example, if we replace AVG with SUM , the result will not be distorted. Adding 0 to the sum will not hurt anyone, but by calculating the average from 0 for unknown values, you will not get the real average.

In this case, I would add price IS NOT NULL to the WHERE to avoid these unknown values.

Answer 2

(this answer was added to provide shorter and more general examples of the question - without including all the details of a particular case in the original question).

---

There are two different "problems" here: the first - if there are no rows in the table or subquery, the second - if the query has NULL values.

For all versions I tested, postgres and mysql will ignore all NULL values ​​when averaging and will return NULL if there is nothing to average. This usually makes sense, since NULL should be considered "unknown." If you want to override this, you can use coalescence (as suggested by Luke M).

 $ create table foo (bar int); CREATE TABLE $ select avg(bar) from foo; avg ----- (1 row) $ select coalesce(avg(bar), 0) from foo; coalesce ---------- 0 (1 row) $ insert into foo values (3); INSERT 0 1 $ insert into foo values (9); INSERT 0 1 $ insert into foo values (NULL); INSERT 0 1 $ select coalesce(avg(bar), 0) from foo; coalesce -------------------- 6.0000000000000000 (1 row) 

Of course, "from foo" can be replaced by "from (... any complex logic here ...) like foo"

Now, should the NULL row in the table be considered 0? Then you need to use the union inside the avg call.

 $ select coalesce(avg(coalesce(bar, 0)), 0) from foo; coalesce -------------------- 4.0000000000000000 (1 row) 

Answer 3

I can come up with 2 ways to achieve this:

• IFNULL ():

  The IFNULL () function returns the specified value if the expression is NULL. If the expression is NOT NULL, this function returns the expression.

Syntax:

IFNULL(expression, alt_value)

Example IFNULL () with your request:

 SELECT AVG( price ) FROM( SELECT *, cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan WHERE listing_Type = 'AARM' AND u_kbalikepartnumbers_id = 1000307 AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48 AND IFNULL( price, 0 ) > ( SELECT AVG( IFNULL( price, 0 ) )* 0.50 FROM ( SELECT *, cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan WHERE listing_Type='AARM' AND u_kbalikepartnumbers_id = 1000307 AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48 ) g WHERE cume_dist < 0.50 ) AND IFNULL( price, 0 ) < ( SELECT AVG( IFNULL( price, 0 ) ) *2 FROM( SELECT *, cume_dist() OVER ( ORDER BY price desc ) FROM web_price_scan WHERE listing_Type='AARM' AND u_kbalikepartnumbers_id = 1000307 AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48 ) d WHERE cume_dist < 0.50) )s HAVING COUNT(*) > 5 

• SOAJEZSE ()

  The COALESCE () function returns the first nonzero value in the list.

Syntax:

COALESCE(val1, val2, ...., val_n)

Example COALESCE () with your request:

 SELECT AVG( price ) FROM( SELECT *, cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan WHERE listing_Type = 'AARM' AND u_kbalikepartnumbers_id = 1000307 AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48 AND COALESCE( price, 0 ) > ( SELECT AVG( COALESCE( price, 0 ) )* 0.50 FROM ( SELECT *, cume_dist() OVER ( ORDER BY price DESC ) FROM web_price_scan WHERE listing_Type='AARM' AND u_kbalikepartnumbers_id = 1000307 AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48 ) g WHERE cume_dist < 0.50 ) AND COALESCE( price, 0 ) < ( SELECT AVG( COALESCE( price, 0 ) ) *2 FROM( SELECT *, cume_dist() OVER ( ORDER BY price desc ) FROM web_price_scan WHERE listing_Type='AARM' AND u_kbalikepartnumbers_id = 1000307 AND ( EXTRACT( DAY FROM ( NOW() - dateEnded ) ) ) * 24 < 48 ) d WHERE cume_dist < 0.50) )s HAVING COUNT(*) > 5