How to list tables in desc order of how many times they’ve been used in MYSQL?

I need to make the script list the names of relational tables together with the total number of times each table has been used.

The results must be listed in the descending order of the total number of times each one of the relational tables has been used.

Here is my code so far:

WITH ALLDRINKS_COUNT AS (
SELECT 'ALLDRINKS' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%ALLDRINKS%'),
SERVES_COUNT AS(
SELECT 'SERVES' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%SERVES%'),
ORDERS_COUNT AS (
SELECT 'ORDERS' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%ORDERS%'),
LIKES_COUNT AS (
SELECT 'LIKES' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%LIKES%'), 
LOCATED_COUNT AS (
SELECT 'LOCATED' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%LOCATED%'), 
DRINKERS_COUNT AS (
SELECT 'DRINKERS' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%DRINKERS%') 

SELECT * FROM ALLDRINKS_COUNT
UNION 
SELECT * FROM SERVES_COUNT
UNION
SELECT * FROM ORDERS_COUNT
UNION 
SELECT * FROM LIKES_COUNT
UNION
SELECT * FROM LOCATED_COUNT
UNION 
SELECT * FROM DRINKERS_COUNT;

And here is what the ouput looks like:

+------------+----------+
| TABLE_NAME | COUNT(*) |
 +------------+----------+
| ALLDRINKS  |        5 |
| SERVES     |       12 |
| ORDERS     |        6 |
| LIKES      |        5 |
| LOCATED    |        1 |
| DRINKERS   |        2 |
+------------+----------+
6 rows in set (0.00 sec)

The desired output is:

+------------+----------+
| TABLE_NAME | COUNT(*) |
+------------+----------+
| SERVES     |       12 |
| ORDERS     |        6 |
| ALLDRINKS  |        5 |
| LIKES      |        5 |
| DIRNKERS   |        2 |
| LOCATED    |        1 |
+------------+----------+
6 rows in set (0.00 sec)

Answer

Try subquerying your query like this

SELECT TABLE_NAME, COUNT_A FROM(

WITH ALLDRINKS_COUNT AS (
SELECT 'ALLDRINKS' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%ALLDRINKS%'),
SERVES_COUNT AS(
SELECT 'SERVES' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%SERVES%'),
ORDERS_COUNT AS (
SELECT 'ORDERS' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%ORDERS%'),
LIKES_COUNT AS (
SELECT 'LIKES' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%LIKES%'), 
LOCATED_COUNT AS (
SELECT 'LOCATED' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%LOCATED%'), 
DRINKERS_COUNT AS (
SELECT 'DRINKERS' TABLE_NAME, COUNT(*)
FROM mysql.general_log
WHERE argument LIKE '%DRINKERS%') 

SELECT TABLE_NAME, COUNT(*) AS COUNT_A FROM ALLDRINKS_COUNT
UNION 
SELECT TABLE_NAME, COUNT(*) AS COUNT_A FROM SERVES_COUNT
UNION
SELECT TABLE_NAME, COUNT(*) AS COUNT_A FROM ORDERS_COUNT
UNION 
SELECT TABLE_NAME, COUNT(*) AS COUNT_A FROM LIKES_COUNT
UNION
SELECT TABLE_NAME, COUNT(*) AS COUNT_A FROM LOCATED_COUNT
UNION 
SELECT TABLE_NAME, COUNT(*) AS COUNT_A FROM DRINKERS_COUNT) t
ORDER BY COUNT_A DESC;