Selecting subgroups of a column - SQL (beginner)-CodePudding

My goal is to select genres, and count how many of each gender watch the separate genres (total). The code I have does this, but it does not display 'Male' and 'Female'. Below you can find a sample dataset and the code I have

Genre | Gender
-------------
Comedy| Man
Action| Woman

Goal

Genre  | Gender(M,F)
Comedy | 1M
Action | 1F


SELECT genres, COUNT(gender) AS Gender(M,F) FROM dataset
GROUP BY genres, gender

CodePudding user response：

A few things need to happen , test and transform the first character and enclose the alias, and include gender in group by

SELECT genre, concat(COUNT(gender),
         cast((case when left(gender,1) = 'w' then 'F' else 'M' end) as char(1))) AS 'Gender(M,F)' 
FROM t 
GROUP BY genre, gender

CodePudding user response：

 SELECT genres,gender, COUNT(gender) AS Gender(M,F) 
    FROM dataset 
    GROUP BY genres, gender