January 23, 2013 at 2:38 am
Hi geniuses, I need you guys to fix me a query.
Here's my table
ReceiverGroup PayerGroup Value
GroupA GroupA 1000
GroupA GroupC 3000
GroupA GroupC 6000
GroupB GroupD 2500
GroupB GroupA 5000
GroupB GroupC 2500
GroupE GroupA 1500
GroupE GroupB 3500
Here's the query we need to fix:
SELECT DISTINCT Group, Received, Paid
FROM (
SELECT a.ReceiverGroup AS Group, SUM(a.Value) as Received, 0 as Paid
FROM Test a
Group by a.ReceiverGroup
UNION ALL
SELECT b.PayerGroup as Group, 0 as Received, SUM(b.Value) as Paid
FROM Test b
Group by b.PayerGroup
) AS c
Which gives me:
Group Received Paid
GroupA 0 7500
GroupA 10000 0
GroupB 0 3500
GroupB 10000 0
GroupC 0 11500
GroupD 0 2500
GroupE 5000 0
What I want:
Group Received Paid
GroupA 10000 7500
GroupB 10000 3500
GroupC 0 11500
GroupD 0 2500
GroupE 5000 0
January 23, 2013 at 2:49 am
Just building on what you already have...
SELECT Group, SUM(Received), SUM(Paid)
FROM (
SELECT a.ReceiverGroup AS Group, SUM(a.Value) as Received, 0 as Paid
FROM Test a
Group by a.ReceiverGroup
UNION ALL
SELECT b.PayerGroup as Group, 0 as Received, SUM(b.Value) as Paid
FROM Test b
Group by b.PayerGroup
) AS c
GROUP BY Group
Assuming SUM is the correct aggregation for Received and Paid
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply