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 NULL
s. 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
Splitting SQL Column into Multiple Columns Based on Value
Sql Server Maximum Rows That Can Be Inserted in a Single Insert Statment
Display Each Department's Number and Name and the Number of Employees Employed in Each Department
Mysql - Trigger for Updating Same Table After Insert
How to Merge Rows on Specific Condition
Find Employee Name Who Is Having Highest Salary in Given Month in SQL
Loop Through Table Rows and Call Stored Procedure on Every Row
Ssis Failed Validation and Returned Validation Status "Vs_Isbroken"
Sql Average from Multiple Columns
Percent to Total in Postgresql Without Subquery
How to Combine Two Completely Different SQL Queries into One Result
Sql Join: Selecting the Last Records in a One-To-Many Relationship
How to Find Specific Values in a Table in Oracle
Select All Dates Between First Day of Month and Current Date
Select Ids from Multiple Rows Where Column Values Satisfy One Condition But Not Another
Job for Mysqld.Service Failed See "Systemctl Status Mysqld.Service"
Error 1067 (42000): Invalid Default Value for 'Created_At'
Update Columns in the Same Table With Different Values With Postgres