SQL Syntax Needed for Combining record amounnts

I have a situtation where I need to combine the invidual records into one while also keeping records. This is for an accounting program and here is an example.

Field: TrxNumb
Field: DocAmount
Field: DebitAmt
Field: CreditAmt

Let's say we have these four records:
1001,500,150,0
1001,500,150,0
1001,500,150,0
1001,500,50,0

So the total document amount is $500 made up of 150+150+150+50.
What I am trying to accomplish with one sql statement is to list the four records plus a fifth record which would look like :
1001,500,0,500  where on this record the credit amount in the sum of the four debit amounts. It is the creation of the fifth record that is casing trouble. The common field is the TrxNumb field. The four records actually exist in the table and the fifth record is a virtual record. I need the fifth record to complete the dataset. Can this be accomplished in one SQL statement or perhaps I need to create another table from this data and use that table?
rwheeler23Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Victoria YudinConnect With a Mentor OwnerCommented:
How about something like this:
select TrxNumb, DocAmount, DebitAmt, CreditAmt
from YourTable --replace with yours
union all
select TrxNumb, DocAmount, sum(DebitAmt) DebitAmt, sum(CreditAmt) CreditAmt
from YourTable --replace with yours
group by TrxNumb, DocAmount
order by TrxNumb, DocAmount

Open in new window

0
 
owaisyahyaCommented:
Here is the query that might work in your case

select TrxNumb,DocAmount,DebitAmt,CreditAmt,sum(DebitAmt) as 'CreditAmt' from accounts
0
 
rwheeler23Author Commented:
Wth a little tweaking tis did the trick
0
All Courses

From novice to tech pro — start learning today.