CODEWITHSUNDEEP
mysql
Cris
Cris

Reputation: 437

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

Upvotes: 1

Views: 36

Answers (2)

sagi
sagi

Reputation: 40491

Except from adding an extra column, you can merge the first two selects into 1 :

CREATE VIEW view_summary AS
(SELECT CASE WHEN role_id = 2 THEN 'clients' ELSE 'admins' END as table_name,
       COUNT(*) as total_records FROM users WHERE role_id in (1,2)
GROUP BY CASE WHEN role_id = 2 THEN 'clients' ELSE 'admins' END

UNION ALL

SELECT 'problems',COUNT(*) FROM problems)

Upvotes: 1

1000111
1000111

Reputation: 13519

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)

Upvotes: 1

Related Questions