Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# syntax for sql - easy ?

Posted on 2014-10-07
Medium Priority
141 Views
I have two tables with payments and deductions. How do I substract the payments from the deductions being in two separate tables ?

I need to take this total:
SELECT SUM (PmtRecd) as totalpayments
FROM dbo.BillPaymentsRecvdTA

and substract this total:
SELECT SUM (amount) AS totaltransactions
FROM    dbo.TATransactions

And call it "TotalAvailable"
0
Question by:amucinobluedot
[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

LVL 25

Accepted Solution

Tomas Helgi Johannsson earned 2000 total points
ID: 40367191
Hi!

You do it like this

``````select X.totalpayments - y.totaltransactions as TotalAvailable
from (
SELECT SUM (PmtRecd) as totalpayments
FROM dbo.BillPaymentsRecvdTA ) as X,
(
SELECT SUM (amount) AS totaltransactions
FROM    dbo.TATransactions) as Y
``````

Regards,
Tomas Helgi
0

LVL 58

Expert Comment

ID: 40367194
``````SELECT SUM (PmtRecd) as totalpayments,SUM (amount) AS totaltransactions,sum(PmtRecd)-sum(amount) as TotalAvailable
FROM dbo.BillPaymentsRecvdTA, dbo.TATransactions
``````
0

Author Comment

ID: 40367202
The second option did not do the math correctly for some reason
0

LVL 49

Expert Comment

ID: 40367279
The second option produces a Cartesian product (or "cross join")  between the 2 tables. e.g. if there was 10 rows in the first table and 10 rows in the second table there would be 100 rows in total so any SUM() of the expanded set of rows will be wrong.

The reason this is not a problem in the first solution (by Thomas) is that each subquery produces just one row and 1*1 remains 1, so the unstated cross join of that query does not impact the arithmetic.

I really don't understand why points were awarded to an incorrect answer (sorry Gary)
---------------------------

To AVOID making accidental Cartesian products is SQL queries:
TIP:
Do NOT use commas in the FROM clause

Using ANSI 92 syntax instead of commas to list out tables stops accidental Cartesian products because you actually have to ask for it by using CROSS JOIN
``````SELECT
X.totalpayments - y.totaltransactions AS TotalAvailable
FROM (
SELECT
SUM(PmtRecd) AS totalpayments
FROM dbo.BillPaymentsRecvdTA
) AS X
CROSS JOIN (              --<< here a cross join is OK because 1 * 1 = 1
SELECT
SUM(amount) AS totaltransactions
FROM dbo.TATransactions
) AS Y
;
``````

and the second:
``````SELECT
SUM(PmtRecd)               AS totalpayments
, SUM(amount)                AS totaltransactions
, SUM(PmtRecd) - SUM(amount) AS TotalAvailable
FROM dbo.BillPaymentsRecvdTA
CROSS JOIN dbo.TATransactions --<< why CROSS JOIN here? this would be wrong!!!
;
``````
0

## Featured Post

Question has a verified solution.

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

Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.
###### Suggested Courses
Course of the Month6 days, 20 hours left to enroll