SQL pivot output result set of a COUNT in MySQL without UNION nor GROUP BY-CodePudding

I'm working on couple of millions of lines so I won't use an UNION to display my query as I would like. For design purposes I need this query returned in a certain way to upload automatically a pie chart.

Query:

SELECT
    COUNT(IF( b IS NULL, id , NULL)) AS 'not_assigned',
    COUNT(IF(b IS NOT NULL, id, NULL)) AS 'assigned'
FROM table
WHERE 
    OverType = "abc"
    AND Type = "def"
    AND Sub_Type = "ghi"
    AND Date BETWEEN "2022-12-01" AND "2022-12-25"
    AND Client LIKE '%john%';

Result set:

not_assigned    assigned
1000            500

So I would like to transform the output as this:

                Count
not_assigned    1000
assigned        500

Any advice for a MySQL version 5.0?

CodePudding user response：

You may aggregate by a CASE expression:

SELECT
    CASE WHEN b IS NULL THEN 'not_assigned' ELSE 'assigned' END AS category,
    COUNT(*) AS cnt
FROM yourTable
WHERE 
    OverType = 'abc' AND
    Type = 'def'
    Sub_Type = 'ghi' AND
    Date BETWEEN '2022-12-01' AND '2022-12-25' AND
    Client LIKE '%john%'
GROUP BY 1;