MySQL select in select query-CodePudding

can somebody help me solving and explaining how i can get the following done? : In my MySQL query i want to select all entries, where the forwarded_to_cordroom value is 0, and in the next row i want to have all where the value is 1, basically i could create 2 identical queries, where the only difference would be the WHERE clause (where forwarded_to_cordroom = 1 , where forwarded_to_cordroom = 0) , and i thought about doing this in one query, but getting the following error with what ive tried:

SELECT
        COUNT(DISTINCT o.order_id) as count,
        (SELECT o.forwarded_to_cordroom WHERE o.forwarded_to_cordroom = 1)
FROM
        `orders_articles` o
LEFT JOIN orders oo ON
        o.order_id = oo.order_id
WHERE
        (
            oo.finished_order_date IS NULL OR oo.finished_order_date >= '2021-09-27'
        ) AND oo.order_date <= '2021-09-27'

Results in :

#1140 - In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'o.forwarded_to_cordroom'; this is incompatible with sql_mode=only_full_group_by

I have also tried changing the subselect in various ways (with and without joins etc.) but without success, always the same error.

I'd prefer not turning this mode off, I think that would not be the purpose and that I can fix my query with some help.

Best Regards

CodePudding user response：

Use conditional aggregation:

SELECT COUNT(DISTINCT o.order_id) AS count,
       COUNT(CASE WHEN o.forwarded_to_cordroom = 1 THEN 1 END) AS count_1,
       COUNT(CASE WHEN o.forwarded_to_cordroom = 0 THEN 1 END) AS count_0
FROM orders_articles AS o
LEFT JOIN orders AS oo ON o.order_id = oo.order_id
WHERE ...