Mysql – SQL: How to ask for multiple counts from the same table within a join

heidisqlMySQL

Trying to find 3 separate counts within one column of a table, each with separate WHERE clauses. I've got 2 tables in the query, so one join. Thought this looked correct but it will not run:

SELECT stores.caption,

COUNT (
            WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30' 
            AND tasks.deleted_at IS NULL) 
            AS TaskTotal , 

 COUNT (
            WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30' 
            AND tasks.deleted_at IS NULL 
            AND tasks.completed_at IS NULL) AS TaskOutstanding, 

COUNT (
            WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30'     
            AND tasks.deleted_at IS NULL 
            AND tasks.completed_at IS NULL 
            AND tasks.deadline < '2019-05-16') AS TaskOverdue

    FROM tasks
    FULL JOIN stores ON tasks.store_id=stores.id
    GROUP BY stores.caption;

Best Answer

  1. MySQL does not have FULL join implemented. You are likely to need only a LEFT join (from stores to tasks):

    FROM stores 
     LEFT JOIN tasks
     ON tasks.store_id = stores.id

  2. You can't have WHERE or WHEN in a SELECT expression like you try. There is a FILTER feature in standard SQL but that has not been implemented either in MySQL. There are however CASE .. WHEN expressions you can use just fine. Example:

    COUNT (
    CASE WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30' 
              AND tasks.deleted_at IS NULL
        THEN 1 ELSE NULL END ) 
    AS TaskTotal ,

So the query can be written:

SELECT
    stores.caption,
    COUNT (
        CASE WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30' 
                  AND tasks.deleted_at IS NULL
            THEN 1 ELSE NULL END ) 
        AS TaskTotal ,
    COUNT (
        CASE WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30' 
                  AND tasks.deleted_at IS NULL
                  AND tasks.completed_at IS NULL
            THEN 1 ELSE NULL END ) 
        AS TaskOutstanding ,
    COUNT (
        CASE WHEN tasks.created_at BETWEEN '2019-04-01' AND '2019-04-30' 
                  AND tasks.deleted_at IS NULL
                  AND tasks.completed_at IS NULL
                  AND tasks.deadline < '2019-05-16'
            THEN 1 ELSE NULL END ) 
        AS TaskOverdue
FROM
    stores 
    LEFT JOIN tasks
    ON tasks.store_id = stores.id
GROUP BY
    stores.caption ;

Now, since all 3 expression have a common condition you can simplify by moving that condition to the ON clause - assuming that you don't want any more columns without it.

We can also add table aliases and removed the redundant ELSE NULL from the case expressions:

SELECT
    s.caption,
    COUNT ( t.store_id ) 
        AS TaskTotal ,
    COUNT (
        CASE WHEN t.completed_at IS NULL
            THEN t.store_id END ) 
        AS TaskOutstanding ,
    COUNT (
        CASE WHEN t.completed_at IS NULL
                  AND t.deadline < '2019-05-16'
            THEN t.store_id END ) 
        AS TaskOverdue
FROM
    stores AS s
    LEFT JOIN tasks AS t
    ON t.store_id = s.id
    AND t.created_at BETWEEN '2019-04-01' AND '2019-04-30' 
    AND t.deleted_at IS NULL
GROUP BY
    s.caption ;
Related Question