Search ALL -->
| postgresql return 0 if returned value is null ( 2024-08-29 ) + ( 855 ) | | https://stackoverflow.com/questions/11007009/postgresql-return-0-if-returned-value-is-null | 147
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 no value is available?
sqlpostgresql
Share
Improve this question
Follow
edited Jul 18, 2012 at 5:09
user272735's user avatar
user272735
10.6k99 gold badges7070 silver badges9999 bronze badges
asked Jun 13, 2012 at 1:17
Andrew's user avatar
Andrew
7,7701313 gold badges6666 silver badges120120 bronze badges
1
Are you sure that you're query is well formed ? –
Luc M
Commented Jun 13, 2012 at 1:28
2
@LucM: It can't be a well-formed query. ("having" clause without a "group by" clause.) –
Mike Sherrill 'Cat Recall'
Commented Jun 13, 2012 at 1:42
everything work fine, except that sometimes, when rules are not met, it does not return anything. In addition, how can I gorup by average, I don't think it is possible||whats the point? Multiple selects from web_price_scan are separate selects; not sure what the issue here? –
Andrew
Commented Jun 13, 2012 at 16:08
1
It is Ok to use a having clause without a group by (which defaults to a single group). It acts as a where clause on aggregate results. In this case, the rows are only returned if more than 5 rows are returned by the 1st level subquery. –
bruceskyaus
Commented Aug 22, 2019 at 1:32
Add a comment
3 Answers
Sorted by:
264
use coalesce
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's an example of COALESCE with your query:
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 use with AVG because it modifies the value. NULL means unknown and nothing else. It's not like using it in SUM. In this example, if we replace AVG by SUM, the result is not distorted. Adding 0 to a sum doesn't hurt anyone but calculating an average with 0 for the unknown values, you don't get the real average.
In that case, I would add price IS NOT NULL in WHERE clause to avoid these unknown values.
Share
Improve this answer
Follow
edited Aug 22, 2019 at 4:56
asathkum's user avatar
asathkum
19422 silver badges1717 bronze badges
answered Jun 13, 2012 at 1:23
Luc M's user avatar
Luc M
17.2k2626 gold badges7575 silver badges9090 bronze badges
1
@Andrew I was trying to give you an example using your query. But I get lost. I doubt that this query works. from web_price_scan... seems repeated... –
Luc M
Commented Jun 13, 2012 at 1:31
For those wondering, NULLIF(v1, v2) does pretty much the opposite of COALESCE in that it returns NULL if v1 equals v2. –
s.m.
Commented Jun 8, 2016 at 6:30
Add a comment
Report this ad
46
+500
(this answer was added to provide shorter and more generic examples to the question - without including all the case-specific details in the original question).
There are two distinct "problems" here, the first is if a table or subquery has no rows, the second is if there are NULL values in the query.
For all versions I've tested, postgres and mysql will ignore all NULL values when averaging, and it will return NULL if there is nothing to average over. This generally makes sense, as NULL is to be considered "unknown". If you want to override this you can use coalesce (as suggested by Luc 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 complicated logic here ...) as foo"
Now, should the NULL row in the table be counted as 0? Then coalesce has to be used inside the avg call.
$ select coalesce(avg(coalesce(bar, 0)), 0) from foo;
coalesce
--------------------
4.0000000000000000
(1 row)
Share
Improve this answer
Follow
edited Mar 4, 2020 at 8:44
answered Jun 13, 2012 at 6:38
tobixen's user avatar
tobixen
4,04311 gold badge2121 silver badges2020 bronze badges
Add a comment
4
I can think of 2 ways to achieve this:
IFNULL():
The IFNULL() function returns a specified value if the expression is NULL.If the expression is NOT NULL, this function returns the expression.
Syntax:
IFNULL(expression, alt_value)
Example of IFNULL() with your query:
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
COALESCE()
The COALESCE() function returns the first non-null value in a list.
Syntax:
COALESCE(val1, val2, ...., val_n)
Example of COALESCE() with your query:
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
Share
Improve this answer
Follow
answered Aug 22, 2019 at 6:35
Joish's user avatar
Joish
1,5301919 silver badges2323 bronze badges
18
IFNULL() is not a function in Postgres. This might work in other databases but the question is specifically about Postgres. –
Jon Wilson
Commented Mar 9, 2020 at 12:38
5
In postgres it is NULLIF. Not IFNULL –
SKT
Commented Jun 12, 2023 at 17:35
|
|