i need to count data where two columns could be duplicates or more-CodePudding

name              --              country              --              order
john Abraham      --               USA                 --              burgar
john Abraham      --               USA                 --              cake
john Abraham      --               USA                 --              sugar
CR RONALDO        --               PORT                --              burgar
MESSI GAR         --               ARG                 --              burgar
john Abraham      --               FRA                 --              burgar

i need the result to be

Count
 4

the result is 4 because "john Abraham from USA has 3 results but i counted as 1"

CodePudding user response：

Count distinct is not limited to 1 column

DROP TABLE IF EXISTS T;
create table t (name varchar(3), country varchar(3));

insert into t values
('aaa','usa'),('aaa','usa'),('aaa','usa'),
('bbb','fra'),('ccc','gdr'),
('aaa','uk');

select count(distinct name,country) from t ;

 ------------------------------ 
| count(distinct name,country) |
 ------------------------------ 
|                            4 |
 ------------------------------ 
1 row in set (0.001 sec)

https://mariadb.com/kb/en/count-distinct/

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_count

CodePudding user response：

SELECT COUNT(name) AS count  FROM TABLE WHERE name = 'whatever name'