CODEWITHSUNDEEP
mysql
user4148098
user4148098

Reputation:

How can I write the query bellow efficiently and cleaner?

First, I am selecting from two different databases, db1 and db2. From each database. On db1, I am counting results based on a condition from one column based on the data in that column. Same applies for db2.

    SELECT 
    (SELECT COUNT(*) FROM db1.table1) AS Column1,
    (SELECT COUNT(*) FROM db1.table1 where  
                          table1.X ='a') AS Column2,
    (SELECT COUNT(*) FROM db1.table1 where  
                         table1.X = 'b') AS Column3,
    (SELECT COUNT(*) FROM db1.table1 where 
                         table1.X = 'c') AS Column4,
    (SELECT COUNT(*) FROM db1.table1 where 
                          table1.X = 'd') AS Column5,
    (SELECT COUNT(*) FROM db1.table1 where
                          table1.X = 'e') AS Column6,
    (SELECT COUNT(*) FROM db2.table2 where
                          table2.Y = 'f') AS Column7,
    (SELECT COUNT(*) FROM db2.table2 where
                          table2.Y = 'g') AS Column8,
    (SELECT COUNT(*) FROM db2.table2 where
                          table2.Y = 'h') AS Column9,
    (SELECT COUNT(*) FROM db2.table2 where
                          table2.Y = 'i') AS Column10;

Upvotes: 1

Views: 89

Answers (2)

A. Colonna
A. Colonna

Reputation: 872

Have you tried something like that :

SELECT 
    MAX(Column1) AS Column1,
    MAX(Column2) AS Column2,
    MAX(Column3) AS Column3,
    MAX(Column4) AS Column4,
    MAX(Column5) AS Column5,
    MAX(Column6) AS Column6,
    MAX(Column7) AS Column7,
    MAX(Column8) AS Column8,
    MAX(Column9) AS Column9,
    MAX(Column10) AS Column10
FROM
    (SELECT 
        COUNT(X) AS Column1,
            SUM(X = 'a') AS Column2,
            SUM(X = 'b') AS Column3,
            SUM(X = 'c') AS Column4,
            SUM(X = 'd') AS Column5,
            SUM(X = 'e') AS Column6,
            NULL AS Column7,
            NULL AS Column8,
            NULL AS Column9,
            NULL AS Column10
    FROM
        _ds_stack_table1 AS table1 UNION ALL SELECT 
        NULL AS Column1,
            NULL AS Column2,
            NULL AS Column3,
            NULL AS Column4,
            NULL AS Column5,
            NULL AS Column6,
            SUM(Y = 'f') AS Column7,
            SUM(Y = 'g') AS Column8,
            SUM(Y = 'h') AS Column9,
            SUM(Y = 'i') AS Column10
    FROM
        _ds_stack_table2 AS table2) D;

SQL Fiddle link : http://sqlfiddle.com/#!9/1eb5cf/1/0

Upvotes: 0

manassehkatz-Moving 2 Codidact
manassehkatz-Moving 2 Codidact

Reputation: 3034

If the goal is to get the information but it doesn't have to be exactly those column field name results then I would use two queries:

SELECT `X`,count(*) as `c` FROM `table1` GROUP BY `X` WITH ROLLUP;
SELECT `Y`,count(*) as `c` FROM `table2` GROUP BY `Y` WITH ROLLUP;

You need to loop through the results of each query. Each row will have one identifier (X or Y) and one count (c), except for the last row. The last row will have NULL (naming may vary depending on language you are using to make the queries - PHP, Python, etc.) for the identifier and the total for the count.

This also has the advantage that if you add new values of X or Y that you want to count, the query will not change.

Upvotes: 1

Related Questions