Home -> Usenet -> comp.databases.oracle -> Re: Counting two columns with where causes for each

Re: Counting two columns with where causes for each

From: Matt Novinger <aeaas_at_yahoo.com>
Date: 16 Sep 2002 09:25:24 -0700
Message-ID: <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?
Received on Mon Sep 16 2002 - 18:25:24 CEST












Original text of this message