Why does COALESCE function not work for this query?

3 votes

2 answers

14013 views

                          I am trying to count the number of columns that is retrieved from the subquery, and some subqueries may be empty. So instead of displaying it as an empty cell, I want it to be 0. Found out that I can do this by using the COALESCE function, but for example this query still gives me an empty cell:

    CREATE VIEW third_table AS 
    SELECT * 
    FROM  (second_table  
    NATURAL FULL JOIN (
       SELECT assignment_id, COALESCE( count(*), 0 ) AS num_60_79 
       FROM (
          SELECT assignment_id, mark_as_percent 
          FROM avg_required_table
          WHERE mark_as_percent >= 60 
          AND mark_as_percent < 80
          ) a 
       GROUP BY assignment_id
       ) b);

Is this how COALESCE is supposed to be used?

                        

Asked by Mathu (33 rep)

Mar 12, 2017, 07:32 PM
Last activity: Jul 7, 2022, 01:05 AM

Why does COALESCE function not work for this query?

Related Questions