Ask for SQL

Question

Hi experts:

I have one table like below:

project_id audio_valid audio_type valid_duration person_id gender
431 valid TYKY 3.782 rus00155 M
431 valid TYKY 2.977 rus00155 M
431 valid SHJJ 7.069 rus01297 M
431 valid TYKY 5.046 rus00155 M
431 valid TYKY 2.193 rus00155 M
431 valid TYKY 3.437 rus00155 M

I would like to count the number of Man or Female, and the total number of the valid_duration group by audio_type, the sample like below:

Output:
audio_type tital_valid_duaration Man Female
JYXX 100. 1 0
LYGW 100. 1 0
SHJJ 100. 1 0
TYKY 100. 1 0
TYYL 100. 1 0

And if there are multiple rows having the same person_id and gender, we have to count them as one person.

Thanks for your help.

Answer 1

Hi @Davis ,

After checking, your expected output does not match your sample data. Please provide the correct one. Thanks.

I updated part of your sample data with more conditions.

Please refer below query and check whether it is helpful to you.

create table testda  
(project_id int,  
audio_valid  varchar(10),  
audio_type varchar(10),  
valid_duration decimal(8,3),  
person_id varchar(10),  
gender char(1))  
  
insert into testda values  
(431 ,'valid','TYKY', 3.782,'rus00155','M'),  
(431 ,'valid','TYKY', 2.977,'rus00155','M'),  
(431 ,'valid','SHJJ', 7.069,'rus01297','M'),  
(431 ,'valid','SHJJ', 8.245,'rus01297','M'),  
(431 ,'valid','TYKY', 5.046,'rus00165','M'),  
(431 ,'valid','TYKY', 2.193,'rus00165','M'),  
(431 ,'valid','TYKY', 3.437,'rus00156','F'),  
(431 ,'valid','TYKY', 8.254,'rus00156','F')  

select audio_type,sum(durationtotal) durationtotal,sum(case when gender='M' then 1 else 0 end) Man,sum(case when gender='F' then 1 else 0 end) Female  
from (  
select  audio_type,Person_id,gender,sum(valid_duration) durationtotal  
from testda  
group by audio_type,Person_id,gender) a  
group by audio_type  

Output:

SHJJ	15.314	1	0  
TYKY	25.689	2	1  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.