"David Lozzi" <DavidLozzi@nospam.nospam> wrote in message
news:OS%23SN4b1FHA.1060@TK2MSFTNGP10.phx.gbl...
> Hello,
>
> I have a table with about 30 fields in it. I am returning to asp.net a
> distinct selection of class. From there, I also need to provide a count of
> 2 other field specific values, count of males and females and count of
> race. For example, below is a sample table:
>
> class    gender    race
> 101        m            white
> 101        m            white
> 101        f                hispanic
> 101        f            white
> 101        f            white
> 101        m            hispanic
> and so on....
>
> and here's the result I'm looking for:
>
> class    males    females    white male    white female    hispanic male
> hispanic female
> 101        3            3               2                    2 1
> 1
> 102        4            2                4                    1 0
> 1
> 103        3            4                2                    3 1
> 1
> and so on...
>
> And then from there I'll do math in the asp.net code to provide ratios and
> percentages. How should this be completed? I started developing it by
> looping through the classes then query SQL for each of the stats but that
> didn't last long.. I got several time outs from so many connections.
>
> Is there a way to produce something like this in a proc? or using multiple
> procs?
>
> Any help is greatly appreciated!! Thank you!
>
> --
> David Lozzi
> Web Applications Developer
> dlozzi@(remove-this)delphi-ts.com
>
>
>
>

"Anith Sen" <an***@bizdatasolutions.com> wrote in message
news:ewDGOCc1FHA.2540@TK2MSFTNGP09.phx.gbl...
> You can use something along the lines of:
>
> SELECT class,
>       SUM( CASE WHEN gender = 'm'
>                 THEN 1 ELSE 0 END ) AS "males",
>       SUM( CASE WHEN gender = 'f'
>                 THEN 1 ELSE 0 END ) AS "females",
>       SUM( CASE WHEN gender = 'm'
>                  AND race = 'white'
>                 THEN 1 ELSE 0 END ) AS "white males",
>       SUM( CASE WHEN gender = 'f'
>                  AND race = 'white'
>                 THEN 1 ELSE 0 END ) AS "white females",
>       SUM( CASE WHEN gender = 'm'
>                  AND race = 'hispanic'
>                 THEN 1 ELSE 0 END ) AS "hispanic males",
>       SUM( CASE WHEN gender = 'f'
>                  AND race = 'hispanic'
>                 THEN 1 ELSE 0 END ) AS "hispanic females"
>  FROM tbl
> GROUP BY class ;
>
> --
> Anith
>

"Sophie Guo [MSFT]" <v-s***@online.microsoft.com> wrote in message
news:NPDUWLf1FHA.2244@TK2MSFTNGXA01.phx.gbl...
> Hello David,
>
> I tested the following code and it works fine:
>
> create table t1 (class int, gender varchar(10), race varchar(10))
>
> insert into t1 values (101 ,'m' , 'white')
> insert into t1 values (101 ,'f','hispanic')
> insert into t1 values (101,'f ' ,'white')
> insert into t1 values (101,'f ', 'white')
> insert into t1 values (101,'m ','hispanic')
> insert into t1 values (102,'m ','hispanic')
>
> SELECT CLASS, COUNT(CASE GENDER WHEN 'M' THEN 1 END)AS 'MALES',
>  COUNT(CASE GENDER WHEN 'F' THEN 1 END)AS 'FEMALES',
>  COUNT(CASE GENDER+RACE WHEN 'MWHITE' THEN 1 END)AS 'WHITE MALES',
>  COUNT(CASE GENDER+RACE WHEN 'FWHITE' THEN 1 END)AS 'WHITE FEMALES',
>  COUNT(CASE GENDER+RACE WHEN 'MHISPANIC' THEN 1 END)AS 'HISPANIC MALES',
>  COUNT(CASE GENDER+RACE WHEN 'FHISPANIC' THEN 1 END)AS 'HISPANIC FEMALES'
>
> FROM t1
> group by class
>
> Please note that we need to add the 'group by class' clause.  I hope the
> information is helpful.
>
> Sophie Guo
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
>
> =====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>