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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.