Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: sql question
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 /
SQL> create table CLM_BNRY (clm_id int,
2 pstl_addr_st_cd varchar2(2) )3 /
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 )
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
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