How to use COUNT with multiple columns?

Question

How to use multiple columns with a single COUNT?

Assume that there is a table demo with these data:

id |  col1  |  col2  |    
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*  
1  |'alice' | 'book1'|  
2  |'bob'   | 'book1'|  
3  |'alice' | 'book2'|  
4  |'alice' | 'book3'|

I want to find the count of all the combination of user and book name.

I have tried that if i run with distinct, everything is alright.
select count(distinct col1, col2) from demo

However, when i remove distinct, then the sql statement will not be passed in mysql console.
select count(col1, col2) from demo

Question is how to use COUNT with multiple columns in MySql?

score 17 · Answer 1 · answered Jan 28 '16 at 08:19

There are several things you can count with COUNT() function:

count(*) : rows
count(col1) : rows where col1 is not null
count(col2) : rows where col2 is not null
count(distinct col1) : distinct col1 values.
count(distinct col2) : distinct col2 values.
count(distinct col1, col2) : distinct (col1, col2) values combinations.

Tested at SQLfiddle:

    select 
        count(*)             as count_rows,
        count(col1)          as count_1,
        count(col2)          as count_2,
        count(distinct col1) as count_distinct_1,
        count(distinct col2) as count_distinct_2,
        count(distinct col1, col2) as count_distinct_1_2
    from demo ;

But count(col1, col2) is not valid syntax:

select 
    count(col1, col2) 
from demo ;

gives an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'col2) from demo' at line 2.

score 7 · Answer 2 · answered Nov 12 '19 at 21:16

Obviously, COUNT(DISTINCT) with multiple columns counts unique combinations of the specified columns' values. However, one other important point is that a tuple is counted only if none of the individual values in the tuple is null. If that last aspect of the behaviour is what you are trying to achieve, you could emulate it using a conditional inside COUNT. It could be either the standard-compliant CASE:

SELECT
  COUNT(CASE WHEN col1 IS NOT NULL AND col2 IS NOT NULL THEN 1 END)
FROM
  demo
;

or the MySQL-specific IF function:

SELECT
  COUNT(IF(col1 IS NOT NULL AND col2 IS NOT NULL, 1, NULL))
FROM
  demo
;

where instead of the 1 you can put any non-null constant. A row will be counted only if neither col1 nor col2 is null.

The obvious flaw of this workaround (either variation) is that it is clearly rather unwieldy and will become ridiculously long very quickly as you add more columns to account for.

It is possible to shorten the expression somewhat by choosing a less clear syntax. In particular, you could replace the COUNT with SUM and treat the predicates as numbers (1/0) in an arithmetic expression:

SELECT
  SUM( (col1 IS NOT NULL) * (col2 IS NOT NULL) )
FROM
  demo
;

In the context of the arithmetic operator * the logical result of the IS NOT NULL operator is implicitly converted to a number, 1 for True, 0 for False. With only 1s and 0s the * operator works as an equivalent of the logical AND: the final result will be 1 (True) only if each operand is 1; otherwise the result will be 0 (False). Adding up (SUM) the 1s is equivalent to counting the truths.

So again, the above statement will count only the rows where neither col1 nor col2 is null.

SUM(col1 IS NOT NULL AND col2 IS NOT NULL) should work too, right? — ypercubeᵀᴹ, Mar 05 '21 at 10:28
que: What does and actual do inside a count(col1 and col2) -- what the count() is really counting at? Is there any documentation on it? — Nor.Z, Dec 05 '23 at 21:52
@Nor.Z > COUNT(Expression) is where the expression is non-null only.
https://stackoverflow.com/questions/1354060/mysql-count-and-nulls
So, the and is not doing anything special, group by is (still) needed. — Nor.Z, Dec 06 '23 at 21:19

score 4 · Answer 3 · answered Jul 26 '20 at 01:53

4

Looking at the original question and example. Replace the comma with and.

answered Jul 26 '20 at 01:53

Edward R Stewart

41
1

Nor.Z · Answer 4 · 2023-12-05T22:13:26.507

1

If your intention is to count the number of (col1, col2) (-- both col as a single integral unit)

Then maybe use group by is better.

eg:

select col1, col2, count(*) from demo
group by col1, col2;

Or use PARTITION BY

eg:

select *, count(*) OVER (PARTITION BY col1, col2) as countn from demo;

edited Dec 05 '23 at 22:13

answered Dec 05 '23 at 21:44

Nor.Z

111
3

score 0 · Answer 5 · answered Nov 12 '19 at 18:38

0

select count(*) from (select distinct col1, col2 from demo) as distinctAll

answered Nov 12 '19 at 18:38

Anton Panfilov

9
1

3

I'm confused by your answer. As explained by the OP and confirmed by ypercube's answer, COUNT(DISTINCT col1, col2) already works fine in MySQL, you don't need to work around it by introducing a nested SELECT DISTINCT. What the OP is (or was) having problem with is how to do just COUNT(col1, col2) (without the DISTINCT). Or maybe there's more to what you are suggesting here? Could you perhaps edit your answer with an explanation? (Please note also that COUNT(DISTINCT col1, col2) is not the same as select count(*) from (select distinct col1, col2 from demo) as distinctAll.) – Andriy M Nov 12 '19 at 20:43

HoldOffHunger · Answer 6 · 2021-12-24T19:18:51.630

If you want to get the fastest results, use a UNION():

(
    SELECT
    COUNT(id) AS Table1Count,
    "-" AS Table2Count
    FROM TABLE1
)
UNION
(
    SELECT
    "-" AS Table1Count,
    COUNT(id) AS Table2Count
    FROM TABLE2
)

Actual results:

+------------+-------------+
| Table1Count| Table2Count |
+------------+-------------+
| 11074      | -           |
| -          | 1           |
+------------+-------------+

How to use COUNT with multiple columns?

6 Answers6