Count multiple columns with group by in one query
count, database, group-by, sql
Solution
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
Problem
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.