Solved

VB.net SQL Combine two SQL Statements into one

Posted on 2014-09-15
4
249 Views
Last Modified: 2014-09-16
Hi
I use the following two SQL Statements to get total amount invoiced to customers
and total amount paid by customers. results look like the table in the image.
How do I get the total balance (Amount Invoiced less Amount Paid) using one SQL
statement. What SQL statement would I use

Select [LINK ID] As CustomerID,SUM([AMOUNT]) As AMOUNT From Payments GROUP BY [LINK ID]
Select CustomerID, SUM([Inv Amt]) As AMOUNT From Invoices Group By CustomerID
Image1.jpg
0
Comment
Question by:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 40323668
select
CustomerID, sum(amount) amount
from
 (
   select [Link ID] CustomerID, Amount from payments
   union all
   select customerId, [inv amt] from invoices
) SubQuery
group by CustomerID
0
 

Author Closing Comment

by:Murray Brown
ID: 40323692
Great answer! Thanks very much
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40324640
Just curious, but don't you deduct payments from invoices?

If I invoiced you $1,000 and you paid me $1,000, the outstanding amount is $0; NOT $2,000

Are you sure you just want to add all those values? Seems like a very curious figure that may have little or no meaning
 - it may even be misleading.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40324757
I have subsequently noticed you had a deleted question regarding "VB.net SQL Statement to show balance owed" this makes me even more concerned about the validity of just summing these numbers. Maybe this would help?

CustomerID, sum(amount) amount
from
 (
   select [Link ID] CustomerID, -Amount from payments --<< change this line to make the Amounts values negative
   union all
   select customerId, [inv amt] from invoices
) SubQuery
group by CustomerID
0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question