Alex Bloom Alex Bloom - 1 month ago 11
MySQL Question

trying to use subquery but subquery returns more than one row with qualifiers

I'm writing a query to generate a graph based on 7 day rolling stats. The

newpost
and
closedpost
work all fine, but i'm getting
subquery returns more than one row
when i try to setup a qualifier such as
WHERE closed IS NOT NULL AND (sent = 0 OR sent =1) AND created >= DATE(NOW()) - INTERVAL 7 DAY GROUP BY DATE(created))
to display
solvedpost
. I'm not sure how to go about making this work. Any help would be appriciated

my query:

SELECT
DATE(created) newpostdate,
COUNT(DISTINCT created) newpost,
COUNT(DISTINCT closed) closedpost,
(SELECT
COUNT(DISTINCT closed)
FROM
tickets
WHERE
closed IS NOT NULL
AND (sent = 0 OR sent = 1)
AND created >= DATE(NOW()) - INTERVAL 7 DAY
GROUP BY DATE(created)) solvedpost
FROM
tickets
WHERE
created >= DATE(NOW()) - INTERVAL 7 DAY
GROUP BY DATE(created);

Answer

Use conditional aggregation:

SELECT 
    DATE(created) newpostdate,
    COUNT(DISTINCT created) newpost,
    COUNT(DISTINCT closed) closedpost,
    COUNT(DISTINCT
        CASE WHEN closed IS NOT NULL AND (sent = 0 OR sent = 1)
        THEN closed END
    ) solvedpost
FROM
    tickets
WHERE
    created >= DATE(NOW()) - INTERVAL 7 DAY
GROUP BY DATE(created);

You don't even need the closed IS NOT NULL condition, because NULLs just wan't be counted.

This is based on your original query. But i think you shouldn't use DISTINCT. If you decide not to use DISTICT your query could be:

SELECT 
    DATE(created) newpostdate,
    COUNT(created) newpost,
    COUNT(closed) closedpost,
    SUM(closed IS NOT NULL AND (sent = 0 OR sent = 1)) solvedpost
FROM
    tickets
WHERE
    created >= DATE(NOW()) - INTERVAL 7 DAY
GROUP BY DATE(created);