Home » SQL & PL/SQL » SQL & PL/SQL » Add amounts for similar grouped items?
Add amounts for similar grouped items? [message #304800] Thu, 06 March 2008 15:06 Go to next message
bschmidt
Messages: 11
Registered: November 2007
Junior Member
Thank you for reading...

I want to add and group records as I query the data.

Sample Data

DocType, DocNumber, DocAmount
CT, 100, -50
CT, 100, 50
CT, 200, 150
CT, 300, 25
CT, 300, 25

Output I want...

CT, 100, 0
CT, 200, 150
CT, 300, 50

Can you show me a query that can do this?

Re: Add amounts for similar grouped items? [message #304802 is a reply to message #304800] Thu, 06 March 2008 15:16 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is an example of a very easy and basic use of the SUM function. What did you do in order to solve the problem? Why did it not work? Could you share your code with us so that we could help you? Because, there's no use in offering you the whole code.
Re: Add amounts for similar grouped items? [message #304805 is a reply to message #304800] Thu, 06 March 2008 15:54 Go to previous messageGo to next message
bschmidt
Messages: 11
Registered: November 2007
Junior Member
Here is my production query, what the problem is, is that some of the sums are doubling when they should not. For instance in my example above the CT, 200, 150 record will become CT, 200, 300 when I run the query. Very strange...

SELECT t.doc_typ_cd as documenttype, t.doc_num as documentnumber, t.acmp_dt as receiptdate, t.doc_ln_id as lineid,
t.parn_dbty_ln_id as parnlineid, a.user_dm1 as ccadiv, UPPER(SUBSTR(d.debt_typ_id, 8, 4)) AS debtid, sum(t.dllr_am)
FROM cca_prim_cc_acct p, cca_dbty_ln d, cca_dtla_tran_hist t, mf_ar_actg_dtl_hst a
WHERE UPPER(SUBSTR(p.case_id, 8, 15 )) = 'DTXN597CR000089'
AND p.prty_num = '001'
AND p.uidy = d.parn_acct_id
AND t.doc_cat <> 'PA'
AND d.jsa_id = t.parn_dbty_ln_id (+)
AND t.vend_cd = 'CONV025293'
AND t.doc_num = a.doc_num (+)
group by t.doc_typ_cd, t.doc_num, t.acmp_dt, t.doc_ln_id, t.parn_dbty_ln_id, a.user_dm1, d.debt_typ_id

Re: Add amounts for similar grouped items? [message #304807 is a reply to message #304805] Thu, 06 March 2008 15:56 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Your sample data and output do not match your query.
Re: Add amounts for similar grouped items? [message #304809 is a reply to message #304807] Thu, 06 March 2008 15:59 Go to previous messageGo to next message
bschmidt
Messages: 11
Registered: November 2007
Junior Member
yes, I simplified it, the real output is quite lengthy, but it does describe the problem accurately. The sum(t.dllr_am) is doubling for some of the data where there is one unique record, but it only does it for a few items, not all.
Re: Add amounts for similar grouped items? [message #304858 is a reply to message #304809] Thu, 06 March 2008 23:56 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It means that columns in the GROUP BY clause aren't the same for these "double" records. There *must* be something different, because - if it wasn't - GROUP BY would create only one record.
Previous Topic: generate DDL
Next Topic: sequence
Goto Forum:
  


Current Time: Sun Dec 11 00:33:20 CST 2016

Total time taken to generate the page: 0.07568 seconds