MySQL: How to add additional rows to count number of NULL and NON NULL values?

Question

I have a table that I've transposed (columns to rows) to look like this:

ID    Year   Type   Data
109   2018   A      NULL
105   2019   B      1001
109   2018   A      NULL
105   2019   B      NULL
109   2018   A      1002
105   2019   A      1001
109   2019   A      1002
105   2018   B      NULL
105   2019   B      NULL
109   2019   A      1002

How can I transform the table above into this:

ID   Year   Type   Null_Populated   Record_Count
109  2018   A      NULL             1
109  2018   A      Populated        1
105  2018   A      NULL             0
105  2018   A      Populated        0
109  2018   B      NULL             0
109  2018   B      Populated        0
105  2018   B      NULL             1
105  2018   B      Populated        0
109  2019   A      NULL             0
109  2019   A      Populated        2
105  2019   A      NULL             0
105  2019   A      Populated        1
109  2019   B      NULL             0
109  2019   B      Populated        0
105  2019   B      NULL             2
105  2019   B      Populated        1

Here is the query that I used to convert columns to rows:

select c.ID, 
       t.Year,
       t.Type,
       case c.ID
            when '109' then 109
            when '105' then 105
        end as data
from t2 t
cross join 
(
    select '109' as 109
    union all select '105'
) c

Is there a way to get to what I want by adding to the query above?

Answer 1

That's kind of a messy way to do it in the first place this seems like an easier solution assuming I'm understanding your "what I want" data set correctly:

SELECT ID, YEAR, TYPE, 'Populated' as `Null_Populated`, COUNT(*) as Record_Count FROM t2 WHERE Data IS NOT NULL GROUP BY ID, YEAR, TYPE
UNION ALL
SELECT ID, YEAR, TYPE, 'Null' as `Null_Populated`, COUNT(*) as Record_Count FROM t2 WHERE Data IS NULL GROUP BY ID, YEAR, TYPE

It should also be a lot easier on the DB if your indexes are set up correctly. Your question isn't really very clear regarding what exactly your goal is as the query you wrote doesn't make a lot of sense, especially based on the expected data you posted.

Above works for what it looks like your trying to do (it would not return count(*) = 0 records is the only difference) but I'm not really sure what you're trying to do so I don't know if that's important for your use case or not. It also works for more than 2 IDs, which your query would not if it started scaling.