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

User · Answer

You didn t say which database server you are using  but if temp tables are available they may be the best approach       table is a temp table select     into  table      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      drop may not be required drop table  table

User · Answer

SELECT SUM Output count  Output attr  FROM       SELECT COUNT column1    AS count column1 AS attr FROM tab1 GROUP BY column1      UNION ALL     SELECT COUNT column2  AS count column2 AS attr FROM tab1 GROUP BY column2     UNION ALL     SELECT COUNT column3  AS count column3 AS attr FROM tab1 GROUP BY column3  AS Output      GROUP BY attr

User · Answer

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

User · Answer

select tab1 name  count distinct tab2 id  as tab2 record count count distinct tab3 id  as tab3 record count count distinct tab4 id  as tab4 record count from tab1 left join tab2 on tab2 tab1 id   tab1 id left join tab3 on tab3 tab1 id   tab1 id left join tab4 on tab4 tab1 id   tab1 id

User · Answer

SELECT COUNT col1 OR col2  FROM  table name  GROUP BY col1 col2

User · Answer

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

[sql] Count multiple columns with group by in one query

Examples related to sql

Examples related to database

Examples related to count

Examples related to group-by