0

I Need help have written a query in PostgreSQL 9.2 as below I need to Return rows indicating zero when there’s no results, currently its returning no records even after trying to use COALESCE.

With S as (select test.Name as Test,result.value as result,concat(person.first_name,' ', person.last_name) as "Name",
extract (Year from (select  (age (patient.birth_date)))) as age
from  clinlims.analysis
INNER JOIN clinlims.test on analysis.test_id=test.id
INNER JOIN clinlims.result on analysis.id = result.analysis_id
INNER JOIN clinlims.sample_item on sample_item.id = analysis.sampitem_id
INNER JOIN clinlims.sample_human on sample_human.samp_id = sample_item.samp_id
INNER JOIN clinlims.patient on patient.id = sample_human.patient_id
INNER JOIN clinlims.person on person.id = patient.person_id)

select  Test , coalesce (count(*),0) as "Number Positive" 
from S where result = 'value' and test = 'Haemoglobin' 
group by Test

I have checked the solution here PostgreSQL - count data as zero if it is null (when where clause is used) but it’s not working in my case since I don’t want to move my where condition in the S TABLE since I will be querying from it using different queries later on. Is it possible ?

Collins Oyugi
  • 11
  • 1
  • That's because there are no records matching the filter `where result = 'value' and test = 'Haemoglobin'`. What output do you want when there are no records found? – 404 Mar 15 '19 at 13:43
  • my out put as indicated in the question ,i want it to return zero – Collins Oyugi Mar 17 '19 at 03:37

1 Answers1

0

Consider summing the conditional of WHERE clause:

select  Test, 
        sum((result = 'value' and test = 'Haemoglobin')::integer) as "Number Positive" 
from S
group by Test
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • hey thanks for the quick reply but the above isnt working – Collins Oyugi Mar 17 '19 at 03:39
  • Why isn't it working? Do you receive any errors or undesired results? See this demo using random data: https://rextester.com/MZR65796. The conditional aggregation as shown here does count *Postgres* and *True* rows with zeroes showing. – Parfait Mar 17 '19 at 14:53