Automatically inserting additional columns in MySQL 8.0

Question

Say I have a table like so

+----+----------+------+
| id |   name   | type |
+----+----------+------+
|  1 | apple    | F    |
|  1 | pear     | F    |
|  1 | cucumber | V    |
|  2 | orange   | F    |
|  2 | grass    | NULL |
|  2 | broccoli | V    |
|  3 | flower   | NULL |
|  3 | tomato   | NULL |
+----+----------+------+

I want to end up with a table that counts the number of elements for each type (including NULL types) AND for each id, like this:

+----+-----------------+--------------+--------------+
| id | type_NULL_count | type_F_count | type_V_count |
+----+-----------------+--------------+--------------+
|  1 |               0 |            2 |            1 |
|  2 |               1 |            1 |            1 |
|  3 |               2 |            0 |            0 |
+----+-----------------+--------------+--------------+

This is rather easy to do, but is there a way (a query I can write or something else) such that when I go back and edit one of the type fields in the first table, I end up with a properly updated count table?

For example, let's say I want to add a new type (type X) and change the type field for flower from NULL to X. Is there a way to end up with the following table without having to rewrite the query or add more statements?

+----+-----------------+--------------+--------------+--------------+
| id | type_NULL_count | type_F_count | type_V_count | type_X_count |
+----+-----------------+--------------+--------------+--------------+
|  1 |               0 |            2 |            1 |            0 |
|  2 |               1 |            1 |            1 |            0 |
|  3 |               1 |            0 |            0 |            1 |
+----+-----------------+--------------+--------------+--------------+

I'm not sure if this is the best way to do this, so I am open to suggestions

Consider handling issues of data display in application code. — Strawberry, Jul 23 '19 at 17:21

demircioglu · Accepted Answer · 2019-07-23T20:45:05.973

Having a secondary table which it's number of columns changes based on your first table is not a viable option. Do you need to keep the result in a table or it will be displayed as a report?

I think a better way to do this is using the SQL below calculate counts by id plus type and display using your data display tool the way you like it.

select id, type, count(*) count
from d
group by 1,2
order by 1,2

The output would be

id  type    count
1   F   2
1   V   1
2   F   1
2   V   1
2       1
3   X   1
3       1

Automatically inserting additional columns in MySQL 8.0

1 Answers1