0

I have a query that was given to me that I modified for my use. I'm having issue with the double of total billed amounts. However some accounts the aggregation is correct while in other accounts its double.

my sql is this.

SELECT DISTINCT
CNT.ACCT_ID, 
COUNT(DISTINCT CNT.BILL_ID) AS BILLS,
TO_CHAR(SUM(CNT.CUR_AMT),'9,999,999') as TOTAL_BILLED,
TO_CHAR(SUM(CNT.CUR_AMT)/COUNT (DISTINCT CNT.BILL_ID),'999,999') as AVG_BILL

FROM
(SELECT 
        LC.ACCT_ID,
        BILL.BILL_ID,
        FT.CUR_AMT,
        BILL.BILL_DT

FROM table1.CUSTOMER_DEPOSITS LC,
table2.PS_CI_BSEG BSEG,
table3.PS_CI_BILL BILL,
table4.PS_CI_FT FT

WHERE
LC.ACCT_ID =BILL.ACCT_ID
AND LC.CUST_CLASS NOT IN ('PPAY-R','TAFT','C-TAFT','SP3','C-NPAY')
AND FT.BILL_ID = BILL.BILL_ID
AND FT.FT_TYPE_FLG = 'BS'
AND BSEG.BILL_ID = BILL.BILL_ID
AND BSEG.BSEG_STAT_FLG = '50' 
AND FT.ARS_DT > '01-JUN-2015'
AND FT.ARS_DT < '01-JUL-2016'
)CNT

GROUP BY CNT.ACCT_ID

I ran this against two accounts. One account had the correct total_billed amount while the second account had doubled the total_billed amount

I missing something but I honestly don't know how to resolve this. Any help would be greatly appreciated.

les campbell
  • 1
  • If `ft.ars_dt` has a datatype of `DATE` then `AND FT.ARS_DT > '01-JUN-2015' AND FT.ARS_DT < '01-JUL-2016'` should be `AND FT.ARS_DT > to_date('01-06-2015', 'dd-mm-yyyy') AND FT.ARS_DT < to_date('01-07-2016', 'dd-mm-yyyy')`. As for the "doubling" effect you're seeing, sounds like maybe you're seeing duplicate information due to multiple rows being returned from one (or more) of the tables when perhaps you're only expecting a single row. If I were you, I'd remove the group by and just select everything (e.g. `select *`) and hopefully that will give you somewhere to start looking. – Boneist Sep 19 '16 at 14:39
  • If you need further help, then you need to give us some sample data from each of your tables (and preferably create table statements too) - make sure it shows the issue, i.e. provide data for a "good" and a "bad" account) - along with what you're expecting the results to look like. That way, we'll be able to run your query ourselves and hopefully pinpoint where the problem might be. – Boneist Sep 19 '16 at 14:43
  • Adding to Boneist's suggestion in the first comment above: remove the GROUP BY clause, and ADD to the WHERE clause one more condition: `AND CNT.ACCT_ID = xyz` where `xyz` is an actual account number where you saw the duplication issue. This may help you troubleshoot the problem. – mathguy Sep 19 '16 at 20:18

0 Answers0