Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

syntax for sql - easy ?

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
Aleks
Asked:
Aleks
1 Solution
 
Tomas Helgi JohannssonCommented:
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

Open in new window


Regards,
     Tomas Helgi
0
 
GaryCommented:
SELECT SUM (PmtRecd) as totalpayments,SUM (amount) AS totaltransactions,sum(PmtRecd)-sum(amount) as TotalAvailable
FROM dbo.BillPaymentsRecvdTA, dbo.TATransactions

Open in new window

0
 
AleksAuthor Commented:
The second option did not do the math correctly for some reason
0
 
PortletPaulCommented:
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
;

Open in new window


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!!!
;

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now