Re: Counting two columns with where causes for each

select district,race,count(*) from info where district||race in (select distinct district||race from info where job=15 or date_for_raise is not null)
group by district,race

Mike

"Matt Novinger" <aeaas_at_yahoo.com> wrote in message news:19809582.0209090733.51643fdd_at_posting.google.com...
> I have a table with district, race, job and birthdate that looks
> something like this
>
> table info
> district|race|job|date_for_raise
> 1 | 3 | 4 | 11/8/02
> 25 | 1 | 3 | 5/14/00
>
> district, race, and job are numbers, date is a date, of course
>
> I need a SQL query that will list by district and race the count of
> who has either a job of 15 or has a date for a raise
> the logical query would be
> select district, race, count(job), count(date_for_raise)
> from info
> where job = 18 OR date_for_raise IS NOT NULL
> group by district, race;
>
> but this returns an incorrect total for job=15
>
> I have also tried putting the query each in its own subquery or view,
> but when you do a select calling both views the district and races get
> mixed up because some district/races do not have any jobs that are of
> 15, but do have a date_for_raise and vice versa
>
> any ideas on how to get through this? it seems like it would be a
> simple query
>
> Thank you!
> Matt Novinger
Received on Mon Sep 09 2002 - 20:41:18 CEST