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.

How can the number of rows differ for each column on the same table? — Kermit, Oct 02 '12 at 15:15
Possible duplicate of [How to get multiple counts with one SQL query?](http://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query) — kdauria, Apr 18 '16 at 04:47

score 53 · Accepted Answer · answered Nov 02 '12 at 15:26

53

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

answered Nov 02 '12 at 15:26

coge.soft

1,664
17
24

1

This is a very useful query, especially when you need the count of participating records. For ex, when I need the count of users who have sent me messages, I use a slightly modified version of your query. 'SELECT DateSent, SUM(CASE WHEN TotalMessageCount > 1 THEN 1 ELSE 0 END) UserCount' – Vicky1729 Mar 21 '17 at 17:54

score 11 · Answer 2 · answered Oct 02 '12 at 15:15

11

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

answered Oct 02 '12 at 15:15

John Woo

258,903
69
498
492

Will this not "compute" the table multiple times? – Miserable Variable Oct 02 '12 at 15:17
It would have to calculate it three times regardless. You can't group by three different fields and expect the system to not have to recalculate for each different grouping. On it's face that's impossible. Putting them into a single query may allow the system to reuse the view, but if not I would investigate indexes or, failing that, temporary tables. – Bacon Bits Oct 02 '12 at 15:35
I have indexes in all columns that need count. This query is not optimized, because table must be a subquery (SELECT column1 FROM table JOIN table2 ON ... WHERE ....) and it calculates subqueries again. I was thinking of denormalization so that i don't have joins.. Creating temporary tables is also a little time consuming.. – user666 Oct 02 '12 at 15:44
1

Great. It will be more convenient if you add some description about your query. – Md. Sajedul Karim Apr 09 '19 at 07:01

score 3 · Answer 3 · answered Jul 03 '15 at 06:38

3

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

answered Jul 03 '15 at 06:38

Rajesh Paul

6,793
6
40
57

can you explain why we use distinct here? – Kishan Sep 14 '18 at 04:38

score 1 · Answer 4 · answered Oct 02 '12 at 15:18

1

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

answered Oct 02 '12 at 15:18

Miserable Variable

28,432
15
72
133

Yes i can create temporary tables, (mysql community server) but i don't think it will have a great improve in performance, as creating a temporary table takes me about 1 sec. The queries i do take 0.1-0.2 sec and all together around 1 sec. (Don't you mean CREATE TEMPORARY TABLE AS {query} ?) – user666 Oct 02 '12 at 15:39
I believe on Sybase database servers the above syntax (table name starting with #) creates a temporary table, which is typically stored in memory. That doesn't seem to be the case for you, resulting in expensive disk i/o. With in-memory tables, if the resulting size is not too big temp tables should help – Miserable Variable Oct 02 '12 at 16:12

Mohsen Bandamir · Answer 5 · 2016-10-05T08:12:19.277

1

    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

edited Oct 05 '16 at 08:12

answered Oct 05 '16 at 08:04

Mohsen Bandamir

32
2

score 0 · Answer 6 · answered Dec 09 '20 at 03:44

0

SELECT COUNT(col1 OR col2) FROM [table_name] GROUP BY col1,col2;

answered Dec 09 '20 at 03:44

jackzhang

1

1

Please don't post only code as an answer, but also provide an explanation of what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes – Ran Marciano Dec 09 '20 at 06:27

Count multiple columns with group by in one query

6 Answers6