如果条件独特，则MySQL的不重复计数 [英] MySQL distinct count if conditions unique

问题描述

我正在尝试建立一个查询，该查询告诉我给定数据集中有多少不同的男女。该人由数字 tel标识。相同的电话可能会出现多次，但电话的性别只能计算一次！

I am trying to build a query that tells me how many distinct women and men there are in a given dataset. The person is identified by a number 'tel'. It is possible for the same 'tel' to appear multiple times, but that 'tel's gender should only be counted one time!

7136609221-男性

7136609222-男

7136609223-女

7136609228-男

7136609222-男

7136609223-女

7136609221 - male
7136609222 - male
7136609223 - female
7136609228 - male
7136609222 - male
7136609223 - female

此example_dataset将产生以下内容。

唯一性别总数：4

唯一男性总数：3

唯一女性总数：1

This example_dataset would yield the following.
Total unique gender count: 4
Total unique male count: 3
Total unique female count: 1

我的尝试查询：

SELECT COUNT(DISTINCT tel, gender) as gender_count, 
       COUNT(DISTINCT tel, gender = 'male') as man_count, 
       SUM(if(gender = 'female', 1, 0)) as woman_count 
FROM example_dataset;

实际上有两次尝试。 COUNT（DISTINCT tel，性别='男性'）作为man_count 似乎只返回与 COUNT（DISTINCT tel，性别）-它不考虑那里的限定词。 SUM（if（gender ='female'，1，0））会计算所有女性记录，但不会被DISTINCT电话过滤。

There's actually two attempts in there. COUNT(DISTINCT tel, gender = 'male') as man_count seems to just return the same as COUNT(DISTINCT tel, gender) -- it doesn't take into account the qualifier there. And the SUM(if(gender = 'female', 1, 0)) counts all the female records, but is not filtered by DISTINCT tels.

推荐答案

以下是使用带有 DISTINCT 的子查询的一个选项：

Here's one option using a subquery with DISTINCT:

SELECT COUNT(*) gender_count,
   SUM(IF(gender='male',1,0)) male_count,
   SUM(IF(gender='female',1,0)) female_count
FROM (
   SELECT DISTINCT tel, gender
   FROM example_dataset
) t

• SQL小提琴演示


• SQL Fiddle Demo

如果您不想这样做，也可以使用使用子查询：

This will also work if you don't want to use a subquery:

SELECT COUNT(DISTINCT tel) gender_count,
    COUNT(DISTINCT CASE WHEN gender = 'male' THEN tel END) male_count,  
    COUNT(DISTINCT CASE WHEN gender = 'female' THEN tel END) female_count
FROM example_dataset







• 更多小提琴


• More Fiddle

这篇关于如果条件独特，则MySQL的不重复计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！