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

x
?
Solved

SQL: Union

Posted on 2014-10-21
16
Medium Priority
?
358 Views
Last Modified: 2014-10-21
Is there a better way of doing the below? The first SELECT gets all payments, so all values are positive. The next SELECT statement gets all adjustments, so all values are negative. Together, they are my derived table (JOIN) to get total customer payments (payments - adjustments).
SELECT T.PartyId, SUM(T.Amount) as 'Amount'
FROM [Transaction] T
WHERE T.PaymentTypeId = 1 
GROUP BY T.PartyId

UNION

SELECT Tr.PartyId, SUM(Tr.Amount) as 'Amount'
FROM [Transaction] Tr JOIN [Transaction] T ON Tr.AdjustsTransactionId = T.TransactionId
WHERE Tr.PaymentTypeId = 9 AND 
T.PaymentTypeId = 1  
GROUP BY Tr.PartyId

Open in new window

Results might look like this:
PartyId         Amount
130528         4063.26
130528         -200.00

So I would probably need to drop the above code down a level into a derived table -- call it A. Then, SELECT a SUM of Amount FROM table A, and then group by PartyId. This will be derived table B.

And then from the main select clause, select the amount from table B.

Is there a better strategy?
0
Comment
Question by:pzozulka
[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
  • 7
  • 5
  • 2
  • +1
16 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
ID: 40394775
Not sure about 'better', although I'd recommend using UNION ALL instead of UNION, as UNION eliminates duplicates, and just to make sure that if there's not a duplicate row between the two ( negative payment / two adjustments of the same amount?) that the duplicate isn't eliminated.

>Together, they are my derived table (JOIN) to get total customer payments (payments - adjustments).
Show me what you mean  by this, as I don't see a JOIN here.   A subquery would work though, and depending on whate you're trying to do you can remove the SUM / GROUP BY in the main query above, and add it in the parent query below...

SELECT a.PartyID, a.Amount
FROM (the above query goes here) a

Open in new window

0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1800 total points
ID: 40394777
SELECT T.PartyId, SUM(T.Amount + ISNULL(T9.Amount, 0)) as 'Final_Amount'
FROM [Transaction] T
LEFT OUTER JOIN [Transaction] T9 ON
    T9.PaymentTypeId = 9 AND
    T9.AdjustsTransactionId = T.TransactionId
WHERE
    T.PaymentTypeId = 1
GROUP BY T.PartyId
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40394798
I didn't include the join in the above, but just wanted to make it known that the above code was part of my derived table. But here it is below, using your suggestion of UNION ALL. And just to make sure we're not removing dups, I added the transactionID for uniqueness.

Is there anything wrong with UNION as far as performance goes? My discomfort with it mostly because I don't use it often.

SELECT a.PartyId, SUM(a.amount)
FROM 
(
	SELECT T.TransactionId, T.PartyId, T.Amount
	FROM [Transaction] T
	WHERE T.PaymentTypeId = 1 

	UNION ALL

	SELECT Tr.TransactionId, Tr.PartyId, Tr.Amount
	FROM [Transaction] Tr JOIN [Transaction] T ON Tr.AdjustsTransactionId = T.TransactionId
	WHERE Tr.PaymentTypeId = 9 AND 
	T.PaymentTypeId = 1
) a
GROUP BY a.PartyId

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1800 total points
ID: 40394804
>> Is there anything wrong with UNION as far as performance goes? <<

You're scanning the [Transaction] table at least one extra time, then you have additional I/O to combine the results from the separate scans.  If possible, it's best to do it in a single query, as I posted above.
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 200 total points
ID: 40394817
>Is there anything wrong with UNION as far as performance goes?
UNION eliminates duplicates, which takes some extra time.  UNION ALL does not eliminate duplicates, so no extra time.

Having said that, if you can get to a JOIN solution such as Scott's above, as his statement is correct in that it would eliminate the second SEEK/SCAN on Transaction.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40394852
Interesting...when I ran both queries with "Include Actual Execution Plan", Scott's solution took 59% of the batch, while the UNION took 41%.

If you view Estimated Execution plan, then they say both 50%.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40394857
What's more interesting is that I have to do this twice (two derived tables, in other words, two left joins) because the report requires two different columns, that have slightly different criteria.

This code, take 61% of batch as compared to same criteria using UNION ALL:
SELECT T.PartyId, SUM(T.Amount + ISNULL(T9.Amount, 0)) as 'Final_Amount'
FROM [Transaction] T
LEFT OUTER JOIN [Transaction] T9 ON
    T9.PaymentTypeId = 9 AND
    T9.AdjustsTransactionId = T.TransactionId
WHERE 
    T.PaymentTypeId = 1
GROUP BY T.PartyId

Open in new window


While this code, takes 50% of batch:
SELECT T.PartyId, SUM(T.Amount + ISNULL(T9.Amount, 0)) as 'Final_Amount'
FROM [Transaction] T
LEFT OUTER JOIN [Transaction] T9 ON
    T9.PaymentTypeId = 9 AND
    T9.AdjustsTransactionId = T.TransactionId
WHERE 
    T.PaymentTypeId = 3 AND T.PaymentMethodId = 1
GROUP BY T.PartyId

Open in new window

0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1800 total points
ID: 40394893
The "% of batch" value is useless.  Instead, issue these statements:

SET STATISTICS IO ON
SET STATISTICS TIME ON

and then run both query.  Look at the logical I/Os only, not the physical; in fact, for a more accurate comparison, confirming that there are no physical I/Os in either query, only logical.  That will provide the best comparison.
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40394926
Thanks Scott, do I have to SET these values to OFF after I run the comparison or do they automatically turn off for future queries?
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40394940
OK, so as suggested the stats are below:

UNION query: CPU time = 156 ms, elapsed time = 239 ms, logical reads = 13651, physical reads = 0
JOIN query:     CPU time = 187 ms, elapsed time = 269 ms, logical reads = 10938, physical reads = 0
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40394959
So based on what you said, "look at logical I/Os only", it would seem that the JOIN query has better performance, even though the elapsed time is longer?

These stats don't make sense to me. You would think that the less logical reads, the smaller the elapsed time?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40395021
You would indeed think that.  But remember, we're dealing with 30ms or less, very likely across hyper-threaded CPUs, so that is negligible.

I'm surprised the I/O is that close, though, only 25% more.  Be sure you're not comparing a "double lookup" in the JOIN:
WHERE
    T.PaymentTypeId = 3 AND T.PaymentMethodId = 1
to a single lookup in the UNION ALL.
0
 
LVL 32

Expert Comment

by:awking00
ID: 40395078
select PartyId,
sum(case when PaymentTypeId = 1 then Amount else 0 end) -
sum(case when PaymentTypeId = 9 then Amount else 0 end) as Amount
from Transaction
group by PartyId;
0
 
LVL 32

Expert Comment

by:awking00
ID: 40395097
>>Results might look like this:
PartyId         Amount
130528         4063.26
130528         -200.00<<
In my above query, the results would look like this:
PartyId         Amount
130528         3863.26
To get your results -
select PartyId,
sum(case when PaymentTypeId = 1 then Amount else 0 end) Amount
from Transaction
group by PartyId
union all
select PartyId,
sum(case when PaymentTypeId = 9 then Amount else 0 end) * -1 as Amount
from Transaction
group by PartyId;
0
 
LVL 8

Author Comment

by:pzozulka
ID: 40395136
awking00: I don't think this would work for me. Notice the below line of code from Scott's query:

T9.AdjustsTransactionId = T.TransactionId

This ensures that all PaymentTypeId that equal 9 (Adjustments) are related to the original transactionId.
I can't just say ALL transactions of PaymentTypeId = 9 because then it will find unrelated transactions as well.

Hope that makes sense.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40395275
Also, awking is subtracting a negative number, which will add it instead; i.e.:
If the base data is this:
 PartyId         Amount
 130528         4063.26
 130528         -200.00<<
The query above will yield this, which is incorrect:
 PartyId         Amount
 130528         4263.26
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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

721 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