Re: Counting two columns with where causes for each

aeaas_at_yahoo.com (Matt Novinger) wrote in message news:<19809582.0209160825.79c71d23_at_posting.google.com>...
> Mike and John, thank you for the responses, this is the first time I
> have used news groups with something I am stuck with and I really
> appreciate the help. The real table I am working with has ~ 330,000
> rows so I am forced to limit the results for testing purposes, but I
> believe I am accurately translating everything. Unfortunately neither
> worked for me:
> John, yours returns something like this
>
>
> district race countjobs countdates
> 3 5 3 8
> 3 5 3 8
> 3 5 3 8
> 3 5 3 8
> 3 5 3 8
> 3 5 3 8
> 3 5 3 8
> 3 5 3 8
> 3 5 3 8
> 3 5 3 8
> .....
> 2 2 45 32
> 2 2 45 32
> .....
>
> and so on, it appears to be a row for every employee in the info table
> listing the equivalent row in the first and second view, this is
> without the (+)'s. With (+) you get a row returned for every row in
> the info table so you end up with a bunch of rows with district x and
> race y with nothing in the last two columns in addition to the
> relevent results that are obtained without the (+). It doesn't make
> sense to me as it seems reversed of what it should be, but I am sure I
> am inputting it correctly.
>
> Mike, yours returns all the employees with any job number in a certain
> district and of a certain race along with the number of people that
> have a date in the date_for_raise column, so the problem is its
> returning a count of all rows with any value in jobs, not just the
> rows with a value of 18. It does count correctly for the people who
> have a value in date_for_raise column.
> so it looks something like this:
>
> district race count(jobs) count(date_for_raise)
> 1 5 9723 0
> 2 3 4643 0
> 2 5 4510 0
> 3 5 12978 8
> 4 5 10142 9
> 5 3 1013 1
> 5 4 260 1
> 5 5 7714 15
> 6 5 4453 0
> 7 5 6215 0
> 8 5 7709 0
> 9 5 2029 0
> 10 5 3892 1
>
> If you were to do something like
> select count(jobs)
> from info
> where district = 2 and race = 3 and job = 18
> it returns 11, the correct value
> It appears that the where clause is not being brought up from the
> subquery perhaps?

Am I misunderstanding something here?
Whats wrong with (assuming it's job 15 you want, not 18) ...

SELECT district,

       race,
       SUM(DECODE(job,15,1,0)) jobcount,
       COUNT(date_for_raise) datecount

CE Received on Tue Sep 17 2002 - 15:21:11 CEST