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.
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.
(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)
Use max(coalesce(logincount, 0)) to avoid NULLs
According to Postgres docs (9.6):
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, for example:
Maybe I've missed something obvious here, but wouldn't the NULLIF function be a much better (and more elegant) solution?
Postgres documentation here.
SELECT
COUNT(NULLIF(logincount,0)) AS "Max Login Count",
username,
FROM Test
GROUP BY username
ORDER BY "Max Login Count";
Result:
'Max Login Count' username
----------------------------
0 ff12
1 jb88
2 er11
This is specified by the SQL standard in section 4.16.4 Aggregate functions: :
If no row qualifies, then the result of COUNT is 0 (zero), and the result of any other aggregate function is the null value.
Note that it happens only if all values are NULL or has no values/entries at all.
You can (and should) always test what happens behind the curtains.
Example with custom values:
WITH test_data (a, b) as (
SELECT *
FROM (VALUES
('example1', 1),
('example2', 2),
('example3', NULL),
('example3', 3),
(NULL, NULL),
(NULL, 5),
(NULL, 5),
('example4', NULL)
) t
)
SELECT
a,
SUM(b) AS b
FROM test_data
GROUP BY 1
try coalesce:
The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null
SELECT coalesce(max(code_id) + 1, 1)
FROM configentries
WHERE configtable_id = ...
The short answer is that COALESCE function is what you can use in postgres.
COALESCE is better than IFNULL for several reasons:
COALESCEis a standard SQL function (implemented in ~every RDBMS), whileIFNULLis not standard, even if widely used.COALESCEcan handle more than two operands. It returns the first non-NULL value. For example,COALESCE(NULL, 'a', NULL)returnsa.IFNULLcannot do this.
Take for example:
SELECT y, SUM(x) FROM t GROUP BY y;
t.x is nullable because its value may not be known. But then SUM(x) should also not be known. Why does its behavior default to something like SUM(COALESCE(x, 0)) which may not be desirable?
Is there any alternative?
Hello,
This is my query
SELECT ordertype, status, sum (COUNT (printdate)) over (), date(char(1900000+requestdate)), businessunit FROM Casepallet WHERE date(char(1900000+requestdate)) IN (current date, current date + 1 days) AND business unit=' SCS' AND ordertype!='T1' AND status = 'X' group by ordertype, business unit, request date, status, printdate order by printdate desc limit 1
Sometimes the sum/count of printdate returns nothing, it's null. If it's null I want it to return 0.
I tried doing it like this: COALESCE (sum (COUNT (printdate)) over (), 0), but it doesn't work, it still returns null.
Can anyone guide me here on where I go wrong?