CODEWITHSUNDEEP
sql-servert-sql
Shah
Shah

Reputation: 1654

multiple count in a sql query

i need a report from a database where i need the final result like Number of Male, Number of Female, showing against city and finally against State. I started off with something like.

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

when i do this

   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

it give's me error. incorrect syntax near =. i also tried with select statement

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

But it is also not working. ... Any idea?

Upvotes: 2

Views: 825

Answers (2)

GarethD
GarethD

Reputation: 69769

You could try the PIVOT function if you are using SQL Server 2005 or later:

WITH CTE AS
(   SELECT  p.StateName,  
            d.CityName,
            api.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
)
SELECT  *
FROM    CTE
        PIVOT
        (   COUNT(Gender)
            FOR Gender IN ([Male], [Female])
        ) pvt

Upvotes: 4

Madhivanan
Madhivanan

Reputation: 13700

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

Upvotes: 4

Related Questions