Ask for SQL

Davis 61 Reputation points

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:

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.

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,600 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,191 Reputation points

    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  


    SHJJ	15.314	1	0  
    TYKY	25.689	2	1  

    Best regards,

    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.

0 additional answers

Sort by: Most helpful