CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivot
user1274646
user1274646

Reputation: 921

Group by does not show all the rows

I have a table tblPersonaldata and tblStudentsadmitted

tblPersonalData

UID  Name Gender
------------------------ 
 E1   xyz   M
 E2   pqr   M
 E3   mno   M

tblStudentsadmitted

UID   Status  Stage
----------------------
E1     Y        1
E2     Y        2
E3     Y        1

Now I want the data like this:

Gender  Stage1   Stage2
 M        2       1

But in this case I dont get the data for female gender. I want the data for female gender even if it is null

I have tried this:

select 
    case 
        when gender='M' then 'Male' 
        when gender='F' then 'Female' 
    end as Gender,
    sum(case when Stage=1 then 1 else 0) end as Stage1,
    sum(case when Stage=2 then 1 else 0) end as Stage2
from  tblPersonaldata A inner join 
      tblStudentsadmitted B on A.UID=B.UID 
where B.Status='Y'
group by Gender

Upvotes: 3

Views: 1321

Answers (4)

Taryn
Taryn

Reputation: 247880

In SQL Server, you can use the PIVOT function to generate the result:

select gender,
  Stage1, 
  Stage2
from
(
  select 
    c.gender,
    'Stage'+cast(stage as varchar(10)) Stage
  from (values ('F'),('M')) c (gender)
  left join tblpersonaldata p
    on c.gender = p.gender
  left join tblStudentsadmitted s
    on p.uid = s.uid
    and s.Status='Y'
)src
pivot
(
  count(stage)
  for stage in (Stage1, Stage2)
) piv

See SQL Fiddle with Demo.

Since you are using SQL Server 2008 this query uses the VALUES to generate the list of the genders that you want in the final result set 

from (values ('F'),('M')) c (gender)

Then by using a LEFT JOIN on the other tables the final result will return a row for both the M and F values.

This can also be written using a UNION ALL to generate the list of genders:

select gender,
  Stage1, 
  Stage2
from
(
  select 
    c.gender,
    'Stage'+cast(stage as varchar(10)) Stage
  from 
  (
    select 'F' gender union all
    select 'M' gender
  ) c
  left join tblpersonaldata p
    on c.gender = p.gender
  left join tblStudentsadmitted s
    on p.uid = s.uid
    and s.Status='Y'
)src
pivot
(
  count(stage)
  for stage in (Stage1, Stage2)
) piv

See SQL Fiddle with Demo

The result of both is:

| GENDER | STAGE1 | STAGE2 |
----------------------------
|      F |      0 |      0 |
|      M |      2 |      1 |

Upvotes: 2

user2160561
user2160561

Reputation: 11

SELECT GENDER, 0 AS 'STAGE 0', 1 AS 'STAGE 1', 2 AS 'STAGE 2'
FROM ( SELECT P.ID, GENDER,CASE WHEN STAGE IS NULL THEN 0 ELSE STAGE END STAGE FROM tblPersonaldata P LEFT JOIN tblStudentsadmitted S ON P.UID = S.UID ) AS A PIVOT ( COUNT (ID) FOR STAGE IN ([0],[1],[2]) )P

Upvotes: 0

Kaf
Kaf

Reputation: 33839

This is also working. Using Left joins with a new table (a table with two records for genders M & F).

Fiddle demo

select  t.g Gender,
        isnull(sum(case when Stage = 1 then 1 end),0) Stage1,
        isnull(sum(case when Stage = 2 then 1 end),0) Stage2
from    (values ('M'),('F')) t(g) 
            left join personal a on t.g = a.gender
            left join studentadmitted b on a.uid = b.uid and b.Status = 'Y'
group by t.g
order by t.g

| GENDER | STAGE1 | STAGE2 |
----------------------------
|      F |      0 |      0 |
|      M |      2 |      1 |

Upvotes: 1

John Woo
John Woo

Reputation: 263933

SELECT  CASE WHEN a.Gender = 'M' THEN 'Male' ELSE 'FEMALE' END Gender,
        SUM(CASE WHEN Stage = 1 THEN 1 ELSE 0 END) Stage1,
        SUM(CASE WHEN Stage = 2 THEN 1 ELSE 0 END) Stage2
FROM    personal a
        LEFT JOIN studentadmitted b
            ON a.UID = b.UID AND b.Status = 'Y'
GROUP   BY a.Gender


SELECT  CASE WHEN c.Gender = 'M' THEN 'Male' ELSE 'Female' END Gender,
        SUM(CASE WHEN Stage = 1 THEN 1 ELSE 0 END) Stage1,
        SUM(CASE WHEN Stage = 2 THEN 1 ELSE 0 END) Stage2
FROM    (SELECT 'F' Gender UNION SELECT 'M' Gender) c
        LEFT JOIN personal a
            ON a.Gender = c.Gender
        LEFT JOIN studentadmitted b
            ON a.UID = b.UID AND b.Status = 'Y'
GROUP   BY c.Gender

OUTPUT

╔════════╦════════╦════════╗
║ GENDER ║ STAGE1 ║ STAGE2 ║
╠════════╬════════╬════════╣
║ Female ║      0 ║      0 ║
║ Male   ║      2 ║      1 ║
╚════════╩════════╩════════╝

Upvotes: 3

Related Questions