multiple count in a sql query

Question

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?

See answer below: if you need the output to be exactly as you showed it, it's slightly more tricky. — BonyT
– BonyT, Commented Jul 12, 2012 at 7:32

Madhivanan · Accepted Answer · 2012-07-12 07:38:59Z

4

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

answered Jul 12, 2012 at 7:38

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

BonyT Over a year ago

This works too - but I suspect it will be much slower - speed may not be an issue here though.

ypercubeᵀᴹ Over a year ago

SUM(Gender ='Male') works too in MySQL (because TRUE is 1 and FALSE is 0)

Madhivanan Over a year ago

Thats good ypercube. But it is more generic to MySQL and sometimes may be confuse you :)

ypercubeᵀᴹ Over a year ago

Yes, your query is more db-agnostic. ( I really thought this was tagged with MySQL, was it?). I prefer COUNT() over SUM() but I don't think there is any performance difference: COUNT(CASE WHEN Gender ='Male' THEN 1 ELSE NULL END)

Madhivanan Over a year ago

But won't you get warning about NULL values when you use COUNT over SUM?

GarethD · Accepted Answer · 2012-07-12 08:29:01Z

4

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

edited Jul 12, 2012 at 8:29

answered Jul 12, 2012 at 7:51

GarethD

70k11 gold badges90 silver badges130 bronze badges

1 Comment

Madhivanan Over a year ago

PIVOT was introduced in version 2005 :)

Collectives™ on Stack Overflow

multiple count in a sql query

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related