| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL query help
A copy of this was sent to "Pete" <pmarkey_at_bellsouth.com>
(if that email address didn't require changing)
On Mon, 1 Mar 1999 22:17:59 -0500, you wrote:
>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:
>
[snip]
>
>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
>
try this:
SQL> l
1 select sa_states.state, sa_states.area,
2 sum( decode( sa_stat.prec, 'Snow', 1, 0 ) ) snow
3 from sa_states, sa_stat
4 where sa_states.state=sa_stat.state(+)
5* group by sa_states.area, sa_states.state
SQL> /
ST AREA SNOW -- ------------------------- ---------- CO West 0 GA South 1 HI West 0 IN North 2 NY North 0 TX South 1
6 rows selected.
>
>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
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |