Create Totals By Unique Invoice Number

SQL 2008 / SSRS 2008

INV         Billed            Paid
123         20.00          10.00
123         20.00          10.00
124         15.00            5.00
125         12.00          12.00

I want to create 1 SQL record that I can read in the main report query that gives me the total invoice count, total billed and total paid for all unique invoices. So this one record should show:
    3          47.00           27.00

Or,  better yet, how would I incorporate that into the main report query.
thayduckProgrammer AnalystAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If your requirement is to eliminate duplicates and then Sum/Count, just do a DISTINCT (can also do GROUP BY) in a subquery, like this..

SELECT COUNT(INV) as InvoiceCount, SUM(Billed) as TotalBilled, SUM(Paid) as TotalPaid
FROM (SELECT DISTINCT INV, Billed, Paid FROM YourTable) a

Open in new window

>Or,  better yet, how would I incorporate that into the main report query.
No idea, as I don't see any 'main report query' code here to work with.
0
 
Ganesh GuruduSenior ConsultantCommented:
SELECT inv, COUNT(INV) as InvoiceCount, SUM(Billed) as TotalBilled, SUM(Paid) as TotalPaid FROM table_name
group by inv;
0
 
Pawan KumarDatabase ExpertCommented:
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

Open 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)

Open in new window

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
thayduckProgrammer AnalystAuthor Commented:
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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..
0
 
Pawan KumarDatabase ExpertCommented:
Please show output of your last comment and the expected output from that. Thanks
0
 
thayduckProgrammer AnalystAuthor Commented:
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
0
 
thayduckProgrammer AnalystAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.