Using GROUP BY with UNION 2

[tonydismukes]

I have a table holding information on data sent:

CREATE TABLE DataSent 
(SENDERKEY int,
 RECEIVERKEY int,
 SIZE int,
 PROCESSDATE datetime,
 ... other irrelevant fields ...)

I need to generate a report showing the total quantity of data sent or received by a particular account, within a date range, broken down by trading partner (the party that this account is either sending to or receiving from).

I can get the data I need with this query:

select receiverkey as partner, sum(size)as totalData from DataSent  where senderkey = 42
and (processed between '1/1/2006 00:00:01 AM' and '1/31/2006 11:59:59 PM')
group by receiverkey
union
select senderkey as partner, sum(size)as totalData from DataSent  where receiverkey = 42
and (processed between '1/1/2006 00:00:01 AM' and '1/31/2006 11:59:59 PM')
group by senderkey

The problem is that this gives me 2 rows for each partner - 1 for data sent and 1 for data received, i.e.

Partner totalData
11 80
11 60
14 100
14 130
etc

What I want is to group the totals by Partner, i.e.

Partner totalData
11 140
14 230
etc

How do I accomplish this?

Thanks in advance for any help!

 

[Zathras]

Not sure if this is ANSI or not, but this works in SQL Server:

select CASE 
        WHEN senderkey=42 THEN receiverkey
        ELSE senderkey
       END as srkey,
     sum(size) as totalData
from #DataSent
  where (receiverkey=42 or senderkey=42)
  and (processed between '1/1/2006 00:00:01 AM' and '1/31/2006 11:59:59 PM')
group by CASE 
        WHEN senderkey=42 THEN receiverkey
        ELSE senderkey
       END

 

[tonydismukes]

Zathras, as it so happens, I'm using SQL Server.

Let me check ...

IT WORKS!

Thank you! I don't think I would have gotten that one very quickly on my own.

 

[dnoeth]

Simply put your query into a Derived Table and then group it:

select partner, sum(totalData)
from
(your union-query here
) dt
group by partner


Dieter

 

[tonydismukes]

dnoeth - thanks, that works too. I had tried that, but I didn't know the syntax required providing a name for the derived table, so it wasn't working. I had tried:

select partner, sum(totalData)
from
(your union-query here
)
group by partner

which doesn't work. I neeed a better SQL reference book.

 

[r937]

for SQL Server, that better reference book is Books OnLine (BOL)

r937.com | rudy.ca