Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> SQL query help
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 1Received on Mon Mar 01 1999 - 21:17:59 CST
![]() |
![]() |