sql server programming:
Maybe try this? SELECT school, schoolyear, class, gender, MALE = SUM(CASE WHEN gender='MALE' THEN 1 ELSE 0 END), FEMALE = SUM(CASE WHEN gender='FEMALE' THEN 1 ELSE 0 END) GROUP BY school, schoolyear, class, gender -- Aaron Bertrand SQL Server MVP http://www.aspfaq.com/ [quoted text, click to view] "Adrian" <NoSpam@hotmail.com> wrote in message
news:Oz2Nis2tDHA.1196@TK2MSFTNGP12.phx.gbl...
> Using the following query (hopefully it is self-explanatory):
>
> select school, schoolyear,class, gender, count(gender)
> from schools
> group by school, schoolyear,class, gender
>
> This gives two rows for each class, one for males, one for females. For
> example:
>
> SchoolOne, YearOne, ClassOne, Male,10
> SchoolOne, YearOne, ClassOne, Female,15
>
> What I would like is to have just one row for each class, giving the count
> of males and females, like:
>
> SchoolOne, YearOne, ClassOne,10,15
>
> I would be grateful for any help.
>
> Adrian
>
>
>
>
>
Sorry, little booboo, just copied the other columns. Should look like this. SELECT school, schoolyear, class, MALE = SUM(CASE WHEN gender='MALE' THEN 1 ELSE 0 END), FEMALE = SUM(CASE WHEN gender='FEMALE' THEN 1 ELSE 0 END) GROUP BY school, schoolyear, class -- Aaron Bertrand SQL Server MVP http://www.aspfaq.com/ [quoted text, click to view] "Adrian" <NoSpam@hotmail.com> wrote in message
news:Oz2Nis2tDHA.1196@TK2MSFTNGP12.phx.gbl...
> Using the following query (hopefully it is self-explanatory):
>
> select school, schoolyear,class, gender, count(gender)
> from schools
> group by school, schoolyear,class, gender
>
> This gives two rows for each class, one for males, one for females. For
> example:
>
> SchoolOne, YearOne, ClassOne, Male,10
> SchoolOne, YearOne, ClassOne, Female,15
>
> What I would like is to have just one row for each class, giving the count
> of males and females, like:
>
> SchoolOne, YearOne, ClassOne,10,15
>
> I would be grateful for any help.
>
> Adrian
>
>
>
>
>
Using the following query (hopefully it is self-explanatory):
select school, schoolyear,class, gender, count(gender)
from schools
group by school, schoolyear,class, gender
This gives two rows for each class, one for males, one for females. For
example:
SchoolOne, YearOne, ClassOne, Male,10
SchoolOne, YearOne, ClassOne, Female,15
What I would like is to have just one row for each class, giving the count
of males and females, like:
SchoolOne, YearOne, ClassOne,10,15
I would be grateful for any help.
Adrian
SELECT school, schoolyear, class,
COUNT(CASE gender WHEN 'male' THEN 1 END) AS male,
COUNT(CASE gender WHEN 'female' THEN 1 END) AS female
FROM schools
GROUP BY school, schoolyear, class
--
David Portas
------------
Please reply only to the newsgroup
--
Aaron and David, Thanks very much. They both work nicely. Adrian [quoted text, click to view] "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:iMidnfLTrP8vr1eiRVn-gQ@giganews.com...
> SELECT school, schoolyear, class,
> COUNT(CASE gender WHEN 'male' THEN 1 END) AS male,
> COUNT(CASE gender WHEN 'female' THEN 1 END) AS female
> FROM schools
> GROUP BY school, schoolyear, class
>
> --
> David Portas
> ------------
> Please reply only to the newsgroup
> --
>
>
Don't see what you're looking for? Try a search.
|
