Create Totals By Unique Invoice Number

SELECT inv, COUNT(INV) as InvoiceCount, SUM(Billed) as TotalBilled, SUM(Paid) as TotalPaid FROM table_name
group by inv;

Please try ..full tested solution-

Table Creation and data insertion

CREATE TABLE yourtableName
(
	 INV     INT 
	,Billed   DECIMAL(10,2)         
	,Paid DECIMAL(10,2)
)
GO

INSERT INTO yourtableName VALUES
(123,20.00,10.00	  ),
(123,20.00,10.00	  ),
(124,15.00,5.00	  ),
(125,12.00,12.00	  )
GO

Select allOpen in new window

SOLUTION

SELECT COUNT(*) InvoiceCount,SUM(b) Billed , SUM(p)  Paid
from (
SELECT inv, Billed b, (Paid) p
FROM yourtableName
group by inv,Billed,Paid)l

OUTPUT[code]

/*------------------------

SELECT COUNT(*) InvoiceCount,SUM(b) Billed , SUM(p)  Paid
from (
SELECT inv, Billed b, (Paid) p
FROM yourtableName
group by inv,Billed,Paid)l
------------------------*/
InvoiceCount Billed                                  Paid
------------ --------------------------------------- ---------------------------------------
3            47.00                                   27.00

(1 row(s) affected)

Select allOpen in new window

This is report query:

SELECT Custname,Suffix,InvNo,Stat,Terms,InvDate,Age,DueDate,PastDue,BilledAmt,PaidAmt,NetAmt,Currency,Equipment,WayBillNo,WayBillOSDate,RefBLNo,Origin,Destination,      
     Commodity,OSBillNo,OSCategory,OSType,PromisedAmt,SchPymtDate,eBilliCash,eBill820,CheckNo,PymtCntrlNo,DueDateAPC,AvgPayCycle,Note,ActiveContact,Promised,LastAction,
     Stage,FollowupDate,CommTitle,FollowupType,ProbGroup,ProbNo,ProbStatus,ProbOwnerName1,ProbOwnerName2,ProbReason,ProbReasonDesc,ProbResolverName,ProbContact,    
     replace(replace(replace((LEFT(ProbNoteIndicator, 32767)),'&','&'),''',''''),'"','"') as ProbNoteIndicator,ProbDateEntered,ProbDesc,ProbNextStep,
     ProbRequired,RMSQueueName,TransactionType,ARRep,BusinessUnit,@env AS WhatServer    
FROM   #ARInvDetail

>This is report query:
Ok.  So which column can we use in the report query to JOIN to the set you asked for?    The set in the question is count's and sum's only..

Please show output of your last comment and the expected output from that. Thanks

I created another field in my temp table  called RecID which will only contain a  'X'

ProbRequired,RMSQueueName,TransactionType,ARRep,BusinessUnit,RecID,@env AS WhatServer    
 FROM   #ARInvDetail

This works for me:

SELECT COUNT(InvNo) as InvoiceCount, SUM(BilledAmt) as TotalBilled, SUM(PaidAmt) as TotalPaid, SUM(NetAmt) as TotalNet, SUM(PromisedAmt) as TotalPromisedm, max(RecID) as RecID1 into #Totals11
FROM (SELECT InvNo, max(BilledAmt) as BilledAmt, max(PaidAmt) as PaidAmt, max(NetAmt) as NetAmt, max(PromisedAmt) as PromisedAmt, RecID as RecID FROM #ARInvDetail Group By InvNo, RecID) a


SELECT Custname,Suffix,InvNo,Stat,Terms,InvDate,Age,DueDate,PastDue,BilledAmt,PaidAmt,NetAmt,Currency,Equipment,WayBillNo,WayBillOSDate,RefBLNo,Origin,Destination,      
     Commodity,OSBillNo,OSCategory,OSType,PromisedAmt,SchPymtDate,eBilliCash,eBill820,CheckNo,PymtCntrlNo,DueDateAPC,AvgPayCycle,Note,ActiveContact,Promised,LastAction,
     Stage,FollowupDate,CommTitle,FollowupType,ProbGroup,ProbNo,ProbStatus,ProbOwnerName1,ProbOwnerName2,ProbReason,ProbReasonDesc,ProbResolverName,ProbContact,    
     replace(replace(replace((LEFT(ProbNoteIndicator, 32767)),'&','&'),''',''''),'"','"') as ProbNoteIndicator,ProbDateEntered,ProbDesc,ProbNextStep,
     ProbRequired,RMSQueueName,TransactionType,ARRep,BusinessUnit,RecID,@env AS WhatServer,
     InvoiceCount, TotalBilled, TotalPaid,  TotalNet,  TotalPromisedm,  RecID1
FROM   #ARInvDetail  

Left Join #Totals11 a
  ON a.RecID1 = #ARInvDetail.RecID