Cris Cris - 2 months ago 6
MySQL Question

Getting the total records in multiple tables as a view table (mysql)

Is there a way to get the total records in each table then put it in a view table. My current query is

CREATE VIEW view_summary AS
SELECT COUNT(*) as total_records FROM users WHERE role_id = 2
UNION ALL
(SELECT COUNT(*) FROM users WHERE role_id = 1)
UNION ALL
(SELECT COUNT(*) FROM problems)


the output is

total_records
1
2
1


problem with this query i can't add another column that specify each total records in a row. My goal output is

table_name | total_records
clients | 1
admins | 2
problems | 1


Thanks

Answer

Add another column as table name

CREATE VIEW view_summary AS
SELECT 'cleints' AS table_name,COUNT(*) as total_records FROM users WHERE role_id = 2
UNION ALL
(SELECT 'admins',COUNT(*) FROM users WHERE role_id = 1) 
UNION ALL
(SELECT 'problems',COUNT(*) FROM problems) 
Comments