我有一个表tblPersonaldata
和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
现在我想要的数据是这样的:
Gender Stage1 Stage2
M 2 1
但在这种情况下,我无法获取女性的数据。即使数据为空,我也希望获得女性的数据。
我已经尝试过以下方法:
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