Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

QUERY SALVATION Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 2:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:56 AM
Points: 178, Visits: 668
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

Post #1410422
Posted Wednesday, January 23, 2013 2:49 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:14 PM
Points: 91, Visits: 164,592
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.

I believe in Codd
... and Thinknook is my Chamber of Understanding
Post #1410428
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse