Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Union query help

Posted on 2013-11-29
8
Medium Priority
?
363 Views
Last Modified: 2013-11-30
I am trying to work through this problem and need help.

The data looks like this:

ROID   TransactionType                               Amount
1          Invoice                                   1500
1          Payment - Undeposited Funds              1400
1          Payment - Undeposited Funds               500
2          Invoice                                   2000
2          Payment - Undeposited Funds              1500
2          Payment - Undeposited Funds               500

Open in new window


The results I am looking for is just to see ROID 1 with the amount of 400 which is the total of the multiple payments less the amount of the invoice. In this case they overpaid the invoice by $400.

I was thinking of a union query but I am not getting the results I am expecting. This is what I have so far:
SELECT 
	accountid,
	sum(amount) AS amount
FROM
	(
		(
			SELECT
				ledger.accountid,
				ledger.transactiontype,
				ledger.amount
			FROM
				ledger
			WHERE
				ledger.transactiontype = 'Invoice'
		)
		UNION 
			(
				SELECT
					ledger.accountid,
					ledger.transactiontype,
					Sum(ledger.amount) * - 1 AS amount
				FROM
					ledger
				WHERE
					ledger.transactiontype = 'Payment - Undeposited Funds'
			)
	) x

GROUP BY
	accountid
ORDER BY
	amount

Open in new window


Any thoughts, alternatives, etc is appreciated.
0
Comment
Question by:ckelsoe
[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
8 Comments
 
LVL 18

Assisted Solution

by:Jerry Miller
Jerry Miller earned 800 total points
ID: 39686410
I think that you can do something with a CASE statement and come up with the correct data:

SELECT SUM(CASE WHEN TransactionType = 'Payment - Undeposited Funds' THEN (Amount*-1) Else Amount END) AS TOTAL
FROM ledger
GROUP BY accountid
ORDER BY amount
0
 

Author Comment

by:ckelsoe
ID: 39686415
Well that sort of does what I need. I was expecting around 10 results and this only returned 4 with the totals way off. I may need to rethink how I deal with the problem.

SELECT
  roid,
	SUM(
		CASE
		WHEN TransactionType = 'Payment - Undeposited Funds' THEN
			(Amount *- 1)
		ELSE
			Amount
		END
	) AS TOTAL
FROM
	ledger
GROUP BY
	roid
HAVING
	Round(Sum(ledger.amount), 2) < - 1

Open in new window


I also need to test for the transactiontype = 'Invoice' as there are other transaction types that I do not want to include.
0
 
LVL 4

Assisted Solution

by:rshq
rshq earned 400 total points
ID: 39686416
Hi
 Please test this
SELECT 
	accountid,
	sum(amount) AS amount
FROM
	(
		(
			SELECT
				ledger.accountid,
				ledger.transactiontype,
				ledger.amount
			FROM
				ledger
			WHERE
				ledger.transactiontype = 'Invoice'
		)
		UNION 
			(
				SELECT
					ledger.accountid,
					Min(ledger.transactiontype) as ledger.transactiontype ,
					Sum(ledger.amount) * - 1 AS amount
				FROM
					ledger
				WHERE
					ledger.transactiontype = 'Payment - Undeposited Funds'
                                Group By ledger.accountid
			)
	) x

GROUP BY
	accountid
ORDER BY
	amount

Open in new window

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:ckelsoe
ID: 39686426
Ok - this is returning the results I am looking for I think. I am going to take a look again in the morning to make sure. Thanks

SELECT accountid, 
       Sum(amount) AS amount 
FROM   ((SELECT ledger.accountid, 
                ledger.transactiontype, 
                Round(ledger.amount, 2) AS amount 
         FROM   ledger 
         WHERE  ledger.transactiontype = 'Invoice') 
        UNION 
        (SELECT ledger.accountid, 
                Min(ledger.transactiontype)       AS transactiontype, 
                Round(Sum(ledger.amount) * -1, 2) AS amount 
         FROM   ledger 
         WHERE  ledger.transactiontype = 'Payment - Undeposited Funds' 
         GROUP  BY ledger.accountid)) x 
GROUP  BY accountid 
HAVING Round(Sum(amount), 2) < -1

Open in new window

0
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 400 total points
ID: 39686754
I believe the "Having" clause you keep adding is causing you problems.  Try:
SELECT
    roid,
    SUM(CASE WHEN TransactionType = 'Payment - Undeposited Funds' THEN
           Amount * -1
        ELSE
           Amount
        END
        ) AS TOTAL
FROM ledger
WHERE TransactionType = 'Payment -Undeposited Funds'
   OR TransactionType = 'Invoice'
GROUP BY roid

Open in new window

This would give you:

ROID   Total
1          -400
2               0

If you only want those where the Total is < 0 (a negative balance) then turn that into a subquery and add a where clause:
SELECT ROID, Total
FROM (
SELECT
    roid,
    SUM(CASE WHEN TransactionType = 'Payment - Undeposited Funds' THEN
           Amount * -1
        ELSE
	   Amount
        END
        ) AS TOTAL
FROM ledger
WHERE TransactionType = 'Payment -Undeposited Funds'
   OR TransactionType = 'Invoice'
GROUP BY roid
) as Temp
HAVING Temp.Total < 0

Open in new window

0
 
LVL 29

Assisted Solution

by:Göran Andersson
Göran Andersson earned 400 total points
ID: 39686821
Filter out the records that you want to use in the summation, group them on the account id, and sum up the records:

select
    accountid,
    total = sum(amount * case when transactiontype = 'Invoice' then 1 else -1 end)
from
    ledger
where
    transactiontype  in ('Invoice', 'Payment - Undeposited Funds')
group by
    accountid

Open in new window

0
 
LVL 18

Accepted Solution

by:
Jerry Miller earned 800 total points
ID: 39686988
This one will only add in the two transactionTypes that you are looking for:

SELECT ROID, Total
FROM (SELECT
    roid,
    SUM(CASE WHEN TransactionType = 'Payment - Undeposited Funds' THEN
           Amount * -1
      CASE WHEN TransactionType = 'Invoice' THEN Amount
        END
        ) AS TOTAL
FROM ledger

GROUP BY roid
)
0
 

Author Closing Comment

by:ckelsoe
ID: 39686997
Thanks for the ideas and examples. I have (hopefully) learned some new things about this level of query syntax. This has gotten me close to what I need. I need to rethink the logic based on new information I now have.

In this project an account can have many repair orders (RO). Initially I was told that they wanted to see the credit on the account overall. The code you helped with above does that just fine. Now they want to see the credits applied at each ro. So off we go again.

Anyhow thanks.
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.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

610 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