SQL case statement with aggregate value

Question

I’m trying to transpose columns to rows using SQL case statement.

SELECT
    CASE WHEN a.known = true THEN ‘value1’
         WHEN a.known = true AND b.known = true THEN ‘value2’
         WHEN a.known = true AND b.known = false THEN ‘value3’
         WHEN a.known = true AND b.known = false AND b.aca=true THEN ‘value4’
    END AS ‘Column1’,
    COUNT(distinct a.abc) AS ‘Column2’
FROM a
JOIN b ON a.id = b.id
GROUP BY Column1

What I would like to see would be Column1 with value1, value2... and Column2 with their total.

unfortunately there's no native pivoting function in mysql. You may want to look at this https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql — Ke Zhu, Dec 03 '19 at 04:52
Show us some sample table data and the expected result. All as formatted text, not images. And take a look at [mcve]. — jarlh, Dec 03 '19 at 07:30

score 0 · Answer 1 · answered Dec 03 '19 at 13:11

I think you want:

SELECT COUNT(DISTINCT CASE WHEN a.known = true THEN a.abc END) as value1
       COUNT(DISTINCT CASE WHEN a.known = true AND b.known = true THEN a.abc END) as value2,
       COUNT(DISTINCT CASE WHEN a.known = true AND b.known = false THEN a.abc END) as value3,
       COUNT(DISTINCT CASE WHEN a.known = true AND b.known = false AND b.aca=true THEN a.abc END) as value4
FROM a JOIN
     b
     ON a.id = b.id;

score -1 · Answer 2 · answered Dec 03 '19 at 04:53

I think it is important that we understand what a group by is typically used for.

A COUNT() is an aggregate function that requires a GROUP BY.

Think of it this way Lets count how many "Steves" are in the a database.

We would have something like this:

`SELECT Name, COUNT(Name) AS Name FROM Customers`

This will error because we have no GROUP BY they dont know how to organize the data. So it should be
`SELECT Name, COUNT(Name) AS Name FROM Customers GROUP BY Name"`

In your case:
select

case when a.known=true then ‘value1’

when a.known=true and b.known=true then ‘value2’

when a.known=true and b.known=false then ‘value3’

when a.known=true and b.known=false and b.aca=true then ‘value4’

end as ‘Column1’,

count(distinct a.abc) as ‘Column2’

from a Join b on a.id=b.id

group by column1

`

OP is asking for a pivot function. It's ok to `count(distinct a.abc)` as long as it's already grouped by something even on a different key. the `count` will count non-null values — Ke Zhu, Dec 03 '19 at 04:57

SQL case statement with aggregate value

2 Answers2