Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> SQL query help

SQL query help

From: Pete <pmarkey_at_bellsouth.com>
Date: Mon, 1 Mar 1999 22:17:59 -0500
Message-ID: <UFIC2.147$JS4.191@news1.atl>


I need some help with this query problem: All responses greatly appreciated.

I created simple tables to simplify what I am really trying to do to make the problem easier to solve.

One table holds the state name and area name, the other contains days of rain or snow.

Tables:

SQL> select * from sa_states;

STATE                AREA

-------------------- -----------------
HI West CO West GA South TX South NY North IN North

SQL> select * from sa_stat;

STATE                PREC

-------------------- -----------
HI Rain HI Rain GA Rain GA Snow TX Snow IN Snow IN Snow

I would like to count the number of snow days per state and group by area and then state.
Where a state has no record in the stat table I would like a zero to appear, I would also like a zero to appear if the state is in the stat table but only has rain.

The query listed below gives me the proper count and includes states not in the stat table but because of the second where clause does not return states in the stat table with only rain (i.e. HI) that I want to show up in the results with a count of zero.

Thanks alot for taking a look at this.
Pete

Current Query
SQL> select sa_states.state, sa_states.area, nvl(count(sa_stat.prec),0) as snow
  2 from sa_states, sa_stat
  3 where sa_states.state=sa_stat.state(+)   4 and nvl(sa_stat.prec, 'Snow')='Snow'   5 group by sa_states.area, sa_states.state;

STATE AREA SNOW
-------------------- -------------------- ---------

CO                   West                         0
GA                   South                        1
IN                   North                        2
NY                   North                        0
TX                   South                        1



Received on Mon Mar 01 1999 - 21:17:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US