CODEWITHSUNDEEP
sqlcoalesce
DarkMalice
DarkMalice

Reputation: 205

COALESCE in Nested Query

Trying to stop this query from returning NULL when the results (in this case, the lessons.classDate) doesn't exist. I'm sure COALESCE should be wrapped around but I can't get the syntax right.

SELECT FLOOR(COUNT(S0001)* 100 / 
        (SELECT COUNT(*) FROM stat_P01627
        INNER JOIN lessons ON stat_P01627.lesson = lessons.id 
        INNER JOIN modules ON lessons.module = modules.id 
        WHERE lessons.module = 'MHG405294' AND lessons.classDate >= DATE_ADD(CURDATE(),INTERVAL -12 WEEK))
        ) AS stat12wkMod1 
        FROM attendance_P01627
        INNER JOIN lessons ON stat_P01627.lesson = lessons.id 
        INNER JOIN modules ON lessons.module = modules.id 
        WHERE lessons.module = 'MHG405294' AND lessons.classDate >= DATE_ADD(CURDATE(),INTERVAL -12 WEEK);

I was trying;

SELECT FLOOR(COUNT(S0001)* 100 / 
        COALESCE(SELECT COUNT(*) FROM stat_P01627
        INNER JOIN lessons ON stat_P01627.lesson = lessons.id 
        INNER JOIN modules ON lessons.module = modules.id 
        WHERE lessons.module = 'MHG405294' AND lessons.classDate >= DATE_ADD(CURDATE(),INTERVAL -12 WEEK)),0)
        ) AS stat12wkMod1 
        FROM stat_P01627
        INNER JOIN lessons ON stat_P01627.lesson = lessons.id 
        INNER JOIN modules ON lessons.module = modules.id 
        WHERE lessons.module = 'MHG405294' AND lessons.classDate >= DATE_ADD(CURDATE(),INTERVAL -12 WEEK);

[EDIT] Working version, for those interested.

SELECT IFNULL(FLOOR(COUNT(S0001)* 100 / 
    IFNULL((SELECT COUNT(*) FROM stat_P01627
    INNER JOIN lessons ON stat_P01627.lesson = lessons.id 
    INNER JOIN modules ON lessons.module = modules.id 
    WHERE lessons.module = 'MHG405294' AND lessons.classDate >= DATE_ADD(GETDATE(),INTERVAL -12 WEEK)),1)) ,1)
    AS stat12wkMod1 
    FROM stat_P01627
    INNER JOIN lessons ON stat_P01627.lesson = lessons.id 
    INNER JOIN modules ON lessons.module = modules.id 
    WHERE lessons.module = 'MHG405294' AND lessons.classDate >= DATE_ADD(GETDATE(),INTERVAL -12 WEEK);

Upvotes: 1

Views: 2216

Answers (1)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

Using Isnull for SQL-SERVER and IFNULL for MySQL

SELECT FLOOR(COUNT(S0001)* 100 / 
        IFNULL((SELECT COUNT(*) FROM stat_P01627
        INNER JOIN lessons ON stat_P01627.lesson = lessons.id 
        INNER JOIN modules ON lessons.module = modules.id 
        WHERE lessons.module = 'MHG405294' AND lessons.classDate >= DATE_ADD(CURDATE(),INTERVAL -12 WEEK)),1)
        ) AS stat12wkMod1 
        FROM stat_P01627
        INNER JOIN lessons ON stat_P01627.lesson = lessons.id 
        INNER JOIN modules ON lessons.module = modules.id 
        WHERE lessons.module = 'MHG405294' AND lessons.classDate >= DATE_ADD(CURDATE(),INTERVAL -12 WEEK);

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql

The ISNULL OR IFNULL function and the COALESCE expression have a similar purpose but can behave differently.

Because ISNULL is a function, it is evaluated only once.

As described above, the input values for the COALESCE expression can be evaluated multiple times. Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example.

Upvotes: 1

Related Questions