sql-server - sql查询中的多个计数

Question

我需要来自数据库的报告，其中我需要最终结果，例如男性人数、女性人数，分别针对城市和州显示。我从类似的东西开始。

SELECT     p.StateName,  d.CityName,
 count(api.Gender) as Gender
FROM       dbo.Application_Personal_information as api INNER JOIN
           dbo.state as p ON api.State = p.ID INNER JOIN
           dbo.City as d ON api.City= d.ID
           group by p.StateName, d.CityName

当我这样做的时候

   SELECT     p.StateName,  d.CityName,
     count(api.Gender = 'Male) as Male,
     count(api.Gender = 'Female) as Female,
    FROM       dbo.Application_Personal_information as api INNER JOIN
               dbo.state as p ON api.State = p.ID INNER JOIN
               dbo.City as d ON api.City= d.ID
               group by p.StateName, d.CityName

它给我错误。=附近的语法不正确。我也尝试过使用 select 语句

 COUNT(select api.Gender from api where api.Gender ='Male') as Male,

但它也不起作用。...有什么想法吗？

Answer 1

SELECT     
    p.StateName,  d.CityName, 
    sum(case when Gender ='Male' then 1 else 0 end ) as Male_count,
    sum(case when Gender ='Female' then 1 else 0 end ) as Female_count

FROM
    dbo.Application_Personal_information as api INNER JOIN 
    dbo.state as p ON api.State = p.ID INNER JOIN 
    dbo.City as d ON api.City= d.ID 
group by 
    p.StateName, d.CityName