CODEWITHSUNDEEP
databasepostgresql
Luis Henrique
Luis Henrique

Reputation: 771

Return 0 and null for 0 fetched rows postgresql

I have the following SQL query below, it executes without errors, and is functional, the only thing it is not doing is returning 0 for INT and NULL columns for VARCHAR columns when the query return is 0 rows

QUERY

SELECT
    coalesce((tb_get_customers.customer), 'null') as "CUSTOMER", 
    coalesce((COUNT(*)),0) as "METRICS", 
    coalesce((CONCAT('STATUS: ', severity)), 'null') as "SEVERITY"
FROM 
    tb_get_faults left 
    JOIN tb_get_customers ON tb_get_faults.customer = tb_get_customers.cust_cmdb 
WHERE 
    tb_get_faults.exception = 'NO' 
    AND tb_get_faults.type = 'VM' 
    AND tb_get_faults.customer = 'CLIENT1' 
    AND tb_get_faults.severity= 'HIGH' 
GROUP BY 
    tb_get_faults.customer, 
    tb_get_customers.customer, 
    tb_get_faults.impact 
ORDER BY 
    tb_get_customers.customer ASC;

OUTPUT

enter image description here

DESERIED OUTPUT

------------------------------
CUSTOMER | METRICS | SEVERITY
------------------------------
  null   |    0    |   null

Upvotes: 0

Views: 79

Answers (2)

forpas
forpas

Reputation: 164064

Create a CTE with your query:

with CTE as (
  <your query here>
)
select * from cte
union all
select 'null', 0, 'null'
where not exists (select 1 from cte)

The 2nd query of UNION ALL will return the row that you expect when the CTE does not return any rows.

Upvotes: 1

William Prigol Lopes
William Prigol Lopes

Reputation: 1889

Your-re doing LEFT JOIN instead of INNER JOIN. Probably your tb_get_faults table contains data that are returned.

The columns that you're getting don't contains columns from tb_get_faults table.

Try to put all columns that you're choosing on your group by clause in select, you gonna see exactly what the aggregation are doing.

If you want to show only if you have data on select, change the LEFT JOIN to INNER JOIN

Upvotes: 1

Related Questions