How to Force Postgres to Return 0 Even If There Are No Rows Matching Query, Using Coalesce, Group by and Join

How to force postgres to return 0 even if there are no rows matching query, using coalesce, group by and join

Using your subquery DerivedTable, you could write:

SELECT coalesce(DerivedTable.vol, 0) AS vol,
y.year
FROM (VALUES ('2018'::text)) AS y(year)
LEFT JOIN (SELECT ...) AS DerivedTable
ON DerivedTable.year = y.year;

postgresql return 0 if returned value is null

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.

Get 0 value from a count with no rows

You need to use the COALESCE function in PostgreSQL http://developer.postgresql.org/pgdocs/postgres/functions-conditional.html

Essentially you need to tell SQL how to handle NULLs. i.e. When NULL return 0.

Returning a value even if no result

MySQL has a function to return a value if the result is null. You can use it on a whole query:

SELECT IFNULL( (SELECT field1 FROM table WHERE id = 123 LIMIT 1) ,'not found');

PostgreSQL JOIN without matching NULL values

Treat the right-side fields in the boolean expression as null if they are empty strings, using the NULLIF function, and then the join condition will not return true for rows where both the left & right tables have at least 1 case of '' == ''.

SELECT *
FROM People
LEFT OUTER JOIN Stuff
ON People.PersonID = NULLIF(Stuff.PersonID, '')
OR People.CellNumber = NULLIF(Stuff.PhoneNumber, '')
OR People.Email = NULLIF(Stuff.WorkEmail, '');

SQL LEFT JOIN return 0 rather than NULL

Use:

ISNULL(count(*), 0)


Related Topics



Leave a reply



Submit