Solved

syntax for sql - easy ?

Posted on 2014-10-07
4
135 Views
Last Modified: 2014-10-07
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
Comment
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
  • Learn & ask questions
4 Comments
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 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

Open in new window


Regards,
     Tomas Helgi
0
 
LVL 58

Expert Comment

by:Gary
ID: 40367194
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
 

Author Comment

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

Expert Comment

by:PortletPaul
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
;

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

729 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