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 -> Re: sql question

Re: sql question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 24 Jan 1999 15:34:48 GMT
Message-ID: <36ab3d3a.4170286@192.86.155.100>


A copy of this was sent to andyho99_at_my-dejanews.com (if that email address didn't require changing) On Sun, 24 Jan 1999 07:36:16 GMT, you wrote:

>Hi,
>
>I need to create a report that lists, by state, the number of $50,000
>claims paid. The report layout is something like:
>
>State Name # of 50,000 claims
>---------- ------------------
> CO 6
> NY 8
> . .
> . .
>
>There are 2 tables which I need to access. Table CLM contains tot_bnf_amt
>(claims amount), clm_id (claim ID), table CLM_BNRY contains clm_id, and
>pstl_addr_st_cd (state).
>
>I wrote a simple sqlplus program, it worked OK. However, if there are
>duplicate clm_id in CLM_BNRY table, the result would not be correct. In
>CLM table, clm_id is unique but in CLM_BNRY table, clm_id might be
>duplicate of there are more than one beneficiary. I just want count once
>for each claim ID. With my sql, it will count twice if there are 2 same
>clm_id in CLM_BNRY table. I know I could create a temp. view or table to
>get around this problem but I believe Oracle could handle this in one
>select statement.
>

You don't say what should happen if for 2 rows in clm_bnry with the same claim id, there are different states. This examples shows how to get the results you want picking a 'random' state from the clm_bnry table for a given claim id (i picked the MAX() state for a claim id)...

SQL> create table clm ( clm_id int primary key,

  2                     tot_bnf_amt int )
  3 /
Table created.

SQL> create table CLM_BNRY (clm_id int,

  2                          pstl_addr_st_cd varchar2(2) )
  3 /
Table created.

SQL> insert into clm values ( 1, 10 );
1 row created.

SQL> insert into clm_bnry values ( 1, 'VA' ); 1 row created.

SQL> insert into clm values ( 2, 10 );
1 row created.

SQL> insert into clm_bnry values ( 2, 'VA' ); 1 row created.

SQL> insert into clm_bnry values ( 2, 'PA' ); 1 row created.

SQL> select clm_id, pstl_addr_st_cd, count(*)   2 from ( select a.clm_id, max(b.pstl_addr_st_cd) pstl_addr_st_cd

  3             from clm a, clm_bnry b
  4            where a.clm_id = b.clm_id
  5            group by a.clm_id )

  6 group by clm_id, pstl_addr_st_cd
  7 /

    CLM_ID PS COUNT(*)
---------- -- ----------

         1 VA          1
         2 VA          1





>Following is my sql:
>*************************************************
>break on report
>compute sum of count(b.PSTL_ADDR_ST_CD) on report
>
>select distinct b.PSTL_ADDR_ST_CD, count(b.PSTL_ADDR_ST_CD)
> from clm a, clm_bnry b
> where tot_bnf_amt = 50000 and
> a.clm_id = b.clm_id
> group by b.PSTL_ADDR_ST_CD
>/
>*************************************************
>
>Thanks in advance for your feedback.
>
>Andy
>
>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Jan 24 1999 - 09:34:48 CST

Original text of this message

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