sql question

From: <andyho99_at_my-dejanews.com>
Date: Sun, 24 Jan 1999 07:36:16 GMT
Message-ID: <78eihg$aap$1@nnrp1.dejanews.com>

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.

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 Received on Sun Jan 24 1999 - 01:36:16 CST