CODEWITHSUNDEEP
sqldatabasecountgroup-by
orestisd
orestisd

Reputation: 832

Count multiple columns with group by in one query

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.

Upvotes: 35

Views: 133440

Answers (6)

jackzhang
jackzhang

Reputation: 1

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

Upvotes: 0

Mohsen Bandamir
Mohsen Bandamir

Reputation: 32

    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

Upvotes: 1

Rajesh Paul
Rajesh Paul

Reputation: 7009

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

Upvotes: 3

coge.soft
coge.soft

Reputation: 1674

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

Upvotes: 53

Miserable Variable
Miserable Variable

Reputation: 28752

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

Upvotes: 1

John Woo
John Woo

Reputation: 263693

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

Upvotes: 11

Related Questions