MySQL distinct count if conditions unique
Solution 1
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
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
Solution 2
There is another solution similar to @segeddes's second solution
Select COUNT(DISTINCT tel) as gender_count,
COUNT(DISTINCT IF(gender = "male", tel, NULL)) as male_count,
COUNT(DISTINCT IF(gender = "female", tel, NULL)) as female_count
FROM example_dataset
Explanation :
IF(gender = "male", tel, NULL)
Above expression will return tel if gender is male else it will return NULL value
Then we've
DISTINCT
It will remove all the duplicates
And finally
COUNT(DISTINCT IF(gender = "male", tel, NULL))
Will count all the distinct occurrences of rows having male gender
Note : SQL COUNT function with expression only counts rows with non NULL values, for detailed explanation check - http://www.mysqltutorial.org/mysql-count/
Federico
Updated on November 15, 2020Comments
-
Federico over 3 years
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 - male
7136609222 - male
7136609223 - female
7136609228 - male
7136609222 - male
7136609223 - femaleThis example_dataset would yield the following.
Total unique gender count: 4
Total unique male count: 3
Total unique female count: 1My attempted query:
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;
There's actually two attempts in there.
COUNT(DISTINCT tel, gender = 'male') as man_count
seems to just return the same asCOUNT(DISTINCT tel, gender)
-- it doesn't take into account the qualifier there. And theSUM(if(gender = 'female', 1, 0))
counts all the female records, but is not filtered by DISTINCT tels. -
Federico over 10 years
DISTINCT CASE WHEN gender = 'male' THEN tel END
worked perfect. It was the solution I was looking for. Thanks!! -
balslev almost 9 yearsCOUNT(DISTINCT CASE WHEN ) was also exactly what i was looking for, thanks alot.
-
Madhura about 7 yearsCOUNT(DISTINCT CASE WHEN) worked for me. Thank You So Much.