SQL Query combine 2 rows into 1 adding values

0
votes

I have a query that will potentially return multiple rows for the same ID from my database. This is because it is a payment table and an invoice can be paid on multiple times.

So my results can look like this.

 ID    Company   BillAmount   AmountPaid  
----- --------- ------------ ------------ 
 123   ABC          1000.00       450.00  
 123   ABC          1000.00       250.00  
 456   DEF          1200.00      1200.00

I am building this query to put into Crystal Reports. If I just pull the raw data, I won't be able to do any sub totaling in CR as Bill amount on this will show $3200 when it is really $2200. I'll need to show balance and I can do that in CR but if I am pulling balance on each line returned, the total balance due for all records shown will be wrong as the "duplicate" rows will be counted wrong.

2
sqlcrystal-reports
What query do you use? What do you want to report? – HoneyBadger
Show us db schema, sample data, current and expected output. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. How to create a Minimal, Complete, and Verifiable example Try create a sample in rextester.com – Juan Carlos Oropeza
Use running totals in crystal reports – Siva

2 Answers

0
votes

I am not sure what kind of report you need but maybe a query like this might be useful:

select ID, Company, max(BillAmount), sum(AmountPaid) 
from Payment 
group by ID

-improved after Juan Carlos' suggestion

0
votes

For this, there are 2 option available.

  1. at Crystal report side

    • In crystal report, there is facility to group, as suggested in this link, follow steps
    • for group summary, after add group, put all fields in group footer, check this link

  2. at Sql side the below suggestion (you are not define which sql or db use, I assume Sqlserver 2012 and above)

Get the records with extra 2 column ( TotalBill ,TotalPaid)

 declare @Invoice table(id int , Company varchar(25), BillAmount  int )
 declare @payment table(id int , InvoiceId int, AmountPaid int )

 insert into @Invoice values (1, 'ABC', 1000), (2, 'DFE', 1200)

 insert into @payment values (1, 1, 450), (2, 1, 250), (3, 2, 1200)

 ;with cte as
 ( select sum(BillAmount) TotalBill from @Invoice i  )
 Select 
 i.*, p.AmountPaid ,    
    Sum(AmountPaid)  over ( partition by i.id ) InvoiceWiseTotalPaid,
    cte.TotalBill,
    Sum(AmountPaid) over ( order by i.id ) TotalPaid

from 
    @Invoice i
    Join @payment p on i.id= p.InvoiceId  
    , cte

Output will be

enter image description here