MySQL Create View Tables names from Schema and Count in loop

Question

i need some ideas to create a MySQL View.

The base query collect all table names with prefix _dynamicgroup.

SELECT table_name AS tname FROM information_schema.tables where TABLE_NAME LIKE "%_dynamicgroup%"');

As a next step I would like to have the number of individual fields of each table. In each table there is a column "ID".

SELECT count(id) FROM 10_dynamicgroup 

The view should then look like this:

IDcount	tname
23	10_dynamicgroup
17	33_dynamicgroup
27	3_dynamicgroup
1	56_dynamicgroup
110	18_dynamicgroup

Thank you for your ideas

Answer 1

you can use.

SELECT ... FROM ... GROUP BY ...

</code

Answer 2

You should be querying the information_schema.COLUMNS table:

SELECT COUNT(*) AS IDcount, TABLE_NAME AS name
FROM information_schema.COLUMNS
WHERE TABLE_NAME LIKE '%_dynamicgroup%'
GROUP BY TABLE_NAME;