Ok so this gets me the count of how many Records of type A are blank in column B
SELECT A, Count(B)
FROM `table1`
where
B = ""
group by A
it gives me a table
| A | B |
|---|---|
| First | 564 |
| Second | 1985 |
And that is great. But I want this to summarize by counting blanks in multiple columns, not just blanks in column B, like this:
| A | B | C |
|---|---|---|
| First | 564 | 9001 |
| Second | 1985 | 223 |
I have an intuition that this is done by creating another table first that would look like this
| A | Column | Value |
|---|---|---|
| First | "B" | B value |
| First | "C" | C value |
| Second | "B" | B value |
| Second | "C" | C value |
for every document, so you can count blanks, but I'm not sure how to get there. Is this the right approach? or is there a much simpler version using pivot tables or similar?
CodePudding user response：
You could try using a conditional sum,
select A,
Sum(case when b='' then 1 end) B,
Sum(case when c='' then 1 end) C
from t
group by A