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?

Answer 1

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.