Two counts of rows with different criteria
-
Hello, I have a child table that contains rows related to a parent table. I would like, in a single SELECT query for the parent table, to have two counts, one for rows in the child table that match a certain criteria, and another that match some other criteria. A simple example, a table of
Classrooms, with a child tableStudents, where each student is either male of female; and the query should returnClassroomID,NumMales, andNumFemales. I can easily get only one count (males for example) using GROUP BY with something like:SELECT COUNT(StudentID) AS NumMales FROM Students GROUP BY Sex WHERE Sex = 0but I can't find a way to get two different aggregate functions to work on the same table. In my case it's actually a bit more complicated, because I have three tables related. But a little pointer on how to do it with two tables should help me get started. Any ideas? Thanks! -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
-
Hello, I have a child table that contains rows related to a parent table. I would like, in a single SELECT query for the parent table, to have two counts, one for rows in the child table that match a certain criteria, and another that match some other criteria. A simple example, a table of
Classrooms, with a child tableStudents, where each student is either male of female; and the query should returnClassroomID,NumMales, andNumFemales. I can easily get only one count (males for example) using GROUP BY with something like:SELECT COUNT(StudentID) AS NumMales FROM Students GROUP BY Sex WHERE Sex = 0but I can't find a way to get two different aggregate functions to work on the same table. In my case it's actually a bit more complicated, because I have three tables related. But a little pointer on how to do it with two tables should help me get started. Any ideas? Thanks! -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
-
Like this:
SELECT COUNT(StudentID) AS Females, (SELECT COUNT(StudentID) AS Males FROM dbo.Students WHERE (Sex = 'M')) AS Males FROM dbo.Students AS Students_1 WHERE (Sex = 'F')Got it... thanks!!! :) -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!