none
Spliting the Gender Coumn results into two columns, Male and Female,,mssql RRS feed

  • Question

  • I have students table with columns Training program,name,Gender.....

    what i want is to return the total number of students in each Training program grouped by the gender

    ie.

    TrainingProgram    Male   Female

    Clinical Medicine      34        23

    Medical Lab             23        28

    I have tried the following with no much luck

    SELECT s1.TrainingProgram,COUNT(s1.Sex) AS Male,COUNT(s2.Sex) AS Female FROM Student s1 INNER JOIN Student s2 ON (s1.TrainingProgram = s2.TrainingProgram) WHERE s1.Sex Like 'Male' AND s2.Sex Like 'Female' GROUP BY s1.TrainingProgram,s2.TrainingProgram

    Saturday, April 6, 2013 9:29 AM

Answers

  • declare @t table 
    (trainingprogram varchar(20)
    , name varchar(20)
    , gender char(1))
    insert into @t values
    ('Clinical Medicine', 'person1', 'm'),
    ('Clinical Medicine', 'person2', 'm'),
    ('Clinical Medicine', 'person3', 'f'),
    ('Clinical Medicine', 'person4', 'f'),
    ('Clinical Medicine', 'person5', 'f'),
    ('Medical Lab', 'person6', 'm'),
    ('Medical Lab', 'person7', 'm'),
    ('Medical Lab', 'person8', 'm'),
    ('Medical Lab', 'person9', 'f')
    select * from @t
    
    -- for verification
    select trainingprogram, gender, COUNT(*) as c
    from @t
    group by trainingprogram, gender
    order by trainingprogram
    
    -- query
    ;with cte as (
    select trainingprogram, gender
    from @t
    )
    select * from cte
    pivot (count(gender) for gender in ([f], [m])) as pvt

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Saturday, April 6, 2013 12:55 PM
  • This is another way similar to the derived table approach you were trying for -

    select coalesce(f.trainingprogram, m.trainingprogram) as trainingprogram
    , f.c as f
    , m.c as m
    from
    	(select trainingprogram, COUNT(*) as c
    	from @t
    	where gender = 'f' 
    	group by trainingprogram, gender
    	) as f
    full outer join
    	(select trainingprogram, COUNT(*) as c
    	from @t
    	where gender = 'm'
    	group by trainingprogram, gender
    	) as m
    on f.trainingprogram = m.trainingprogram

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Saturday, April 6, 2013 1:04 PM
  • declare @t table(sno int,firstname varchar(20),Gender bit,Program varchar(20))
    
    Insert into @t
    values (1,'Sam',1,'Medical')
    ,(2,'Jackie',0,'Medical')
    ,(3,'John',1,'Training')
    ,(4,'Rhonda',0,'Intern')
    ,(5,'Samuel',1,'Medical')
    ,(6,'Teri',0,'Medical')
    ,(7,'George',1,'Training')
    ,(8,'Madison',0,'Manager')
    
    select Program,[1] as Male,[0] as Female from (Select gender,PROGRAM from @t) PT PIVOT (count(Gender) for Gender in ([1],[0])) PS


    Hope it Helps!!

    Saturday, April 6, 2013 1:07 PM
    Answerer

All replies

  • Is this what you want?

    SELECT trainingprogram, Gender, count(*)
    FROM 
    ( 
    SELECT trainingprogram, male, female
    FROM student 
    ) Main
    UNPIVOT
    ( 
    Gender FOR Genders IN (male, female) 
    ) Sub
    group by trainingprogram, gender

    You first have to UNPIVOT Male and Female to Gender column. Then you will group them and count.

    • Edited by irusul Saturday, April 6, 2013 9:40 AM
    Saturday, April 6, 2013 9:39 AM
  • What I have in the student table is one column Gender, i want to split it into Male and Female counting the result.

    the way i see it is did it the other round

    the result should look like this

    TrainingProgram    Male   Female

    Clinical Medicine      34        23

    Medical Lab             23        28

    thank so much for the quick reply...

    Saturday, April 6, 2013 10:11 AM
  • declare @t table 
    (trainingprogram varchar(20)
    , name varchar(20)
    , gender char(1))
    insert into @t values
    ('Clinical Medicine', 'person1', 'm'),
    ('Clinical Medicine', 'person2', 'm'),
    ('Clinical Medicine', 'person3', 'f'),
    ('Clinical Medicine', 'person4', 'f'),
    ('Clinical Medicine', 'person5', 'f'),
    ('Medical Lab', 'person6', 'm'),
    ('Medical Lab', 'person7', 'm'),
    ('Medical Lab', 'person8', 'm'),
    ('Medical Lab', 'person9', 'f')
    select * from @t
    
    -- for verification
    select trainingprogram, gender, COUNT(*) as c
    from @t
    group by trainingprogram, gender
    order by trainingprogram
    
    -- query
    ;with cte as (
    select trainingprogram, gender
    from @t
    )
    select * from cte
    pivot (count(gender) for gender in ([f], [m])) as pvt

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Saturday, April 6, 2013 12:55 PM
  • This is another way similar to the derived table approach you were trying for -

    select coalesce(f.trainingprogram, m.trainingprogram) as trainingprogram
    , f.c as f
    , m.c as m
    from
    	(select trainingprogram, COUNT(*) as c
    	from @t
    	where gender = 'f' 
    	group by trainingprogram, gender
    	) as f
    full outer join
    	(select trainingprogram, COUNT(*) as c
    	from @t
    	where gender = 'm'
    	group by trainingprogram, gender
    	) as m
    on f.trainingprogram = m.trainingprogram

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Saturday, April 6, 2013 1:04 PM
  • declare @t table(sno int,firstname varchar(20),Gender bit,Program varchar(20))
    
    Insert into @t
    values (1,'Sam',1,'Medical')
    ,(2,'Jackie',0,'Medical')
    ,(3,'John',1,'Training')
    ,(4,'Rhonda',0,'Intern')
    ,(5,'Samuel',1,'Medical')
    ,(6,'Teri',0,'Medical')
    ,(7,'George',1,'Training')
    ,(8,'Madison',0,'Manager')
    
    select Program,[1] as Male,[0] as Female from (Select gender,PROGRAM from @t) PT PIVOT (count(Gender) for Gender in ([1],[0])) PS


    Hope it Helps!!

    Saturday, April 6, 2013 1:07 PM
    Answerer