mysql how to use results of one query in a few unions

Question

EDIT: mysql version: 5.7.34
I am querying my data to get counts on different group, I'm doing it in one query using union (see this question) here is my query

SELECT 'department' as group_name, department, NULL AS d_type, NULL AS country, NULL AS city, COUNT(trx.id)
FROM (<some query>) trx
GROUP BY department

UNION ALL

SELECT 'd_type', NULL, d_type, NULL, NULL, COUNT(trx.id)
FROM (<some query>) trx
GROUP BY d_type

UNION ALL

SELECT 'location', NULL, NULL, NULL, country, city, COUNT(trx.id)
FROM (<some query>)
GROUP BY country, city

this is my 'some query':

SELECT IFNULL(parent_id, child_id) as id,  department, d_type, country, city
FROM my_table
WHERE <some conditions..>
GROUP BY id

is there a way I can use the 'some query' in all unions with out writing it each time, and with running it only once? (currently it runs for each union)

Create a VIEW for your query: https://dev.mysql.com/doc/refman/8.0/en/create-view.html — forpas, Aug 22 '21 at 20:56

score 1 · Answer 1 · answered Aug 22 '21 at 20:58

1

If you're using MySQL 8.x, you can use a CTE.

WITH trx AS (
    SELECT IFNULL(parent_id, child_id) as id,  department, d_type, country, city
    FROM my_table
    WHERE <some conditions..>
    GROUP BY id
)
SELECT ...
FROM trx
GROUP BY department

UNION ALL

SELECT ...
FROM trx
GROUP BY d_type

UNION ALL

SELECT ...
FROM trx
GROUP BY country, city

If not, define it as a view.

answered Aug 22 '21 at 20:58

Barmar

741,623
53
500
612

mysql version: 5.7.34, when using a view, it will run the 'some query' only once? my problem is not to write the query each time- but rather improving performance – dina Aug 22 '21 at 21:10
1

See https://stackoverflow.com/questions/10302615/mysql-views-performance – Barmar Aug 22 '21 at 21:13

mysql how to use results of one query in a few unions

1 Answers1