Count multiple columns with group by in one query

Question

I count values from multiple columns like this:

SELECT COUNT(column1),column1 FROM table GROUP BY column1 SELECT COUNT(column2),column2 FROM table GROUP BY column2 SELECT COUNT(column3),column3 FROM table GROUP BY column3 

This returns for example for column1 array(attr1 => 2000, attr2 => 3000...) (Each column has specific and few values). The problem is that "table" in my application can be a query with some joins and where clauses, that may take 0.1sec. By doing all that counts "table" is computed each time again which is not necessary. Is there any way to take the results i want with one query, or "cache" the query that produces table? Otherwise i believe denormalization would be the only solution here. And i want the same results with the above queries. I am using mysql-myisam.

Answer 1

It's hard to know how to help you without understanding the context / structure of your data, but I believe this might help you:

SELECT       SUM(CASE WHEN column1 IS NOT NULL THEN 1 ELSE 0 END) AS column1_count     ,SUM(CASE WHEN column2 IS NOT NULL THEN 1 ELSE 0 END) AS column2_count     ,SUM(CASE WHEN column3 IS NOT NULL THEN 1 ELSE 0 END) AS column3_count FROM table 

Answer 2

One solution is to wrap it in a subquery

SELECT * FROM (     SELECT COUNT(column1),column1 FROM table GROUP BY column1     UNION ALL     SELECT COUNT(column2),column2 FROM table GROUP BY column2     UNION ALL     SELECT COUNT(column3),column3 FROM table GROUP BY column3 ) s