mysql - SQL COUNT if a specific value is found and output in the same line

Question

I need some help with the query below.
I have for example the following data set:

and I need to get the following output:

I tried with a query similar to this one:

SELECT 
    id, ValA, count(1)
FROM dual
GROUP BY id, ValA;

but it is not working as expected. It's basically duplicating the values in the output:


Would you be able to help me?

标签： mysqlsqlgroup-bycountcase

Answer 1

count(*)计算所有行。count(ValA) 计算非空值。这意味着count(*) - count(ValA)计算空值。

SELECT 
    id, count(*) - count(ValA), count(ValA)
FROM dual
GROUP BY id;