0

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;
0

1 Answer 1

1
  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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.