1

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?

3

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.

  • that's right, i get the same error about using count(col1, col2). what makes me confused is that whether mysql doc mention this illegal usage. UPDATED i have find that there is no doc about using count(col1, col2) without distinct. – andy Jan 28 '16 at 8:48
  • 1
    No doc == invalid. – Rick James Jan 29 '16 at 3:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.