Aggregation and duplicated line with a left join T-SQL

Question

I have the data that you can see bellow based on this SQL script (SQL Fiddle)


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Id | Emitter    |   EmitterIBAN                         |  Receiver    |   ReceiverIBAN                           |         Adresss                          |     Value 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1,   Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0002,        121.72
2,   Keene,         SK81 1004 7484 7505 6308 9259,        Torrance,       RO23 ZWTR OJKK VAU9 T5P4 2GDY,                  35197 Green Ridge Way,                   82.52
3,   Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0048,        51.81
4,   Korie,         ME43 9833 9830 7367 4239 60,Roy,      IL69            9686 1536 8102 2219 165,                        5 Swallow Alley,                         88.01
5,   Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0001,        133.99
6,   Charmine,      BG92 TOXX 8380 785I JKRQ JS,          Sarette,        MU67 RYRU 9293 5875 6859 7111 075X HR,          8 Sage Place,                            36.30
7,   Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point         0004,        186.99

And I would like to get a result like this one below

Calculate the number of operation that a couple of EmitterIBAN and ReceiverIBAN made
Calculate the sum values for each couple of EmitterIBAN and ReceiverIBAN


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NumberOperation | Emitter    |   EmitterIBAN                         |  Receiver    |   ReceiverIBAN                           |         Adresss                          |     SumValue 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4,                Ernst,         HR53 8827 2118 4692 8207 5,           Kimbra,         CH20 1042 6T0N MDTG JT47 U,                     3256 Arrowood Point           0002,      494,51
1,                Keene,         SK81 1004 7484 7505 6308 9259,        Torrance,       RO23 ZWTR OJKK VAU9 T5P4 2GDY,                  35197 Green Ridge Way,                   82.52
1,                Korie,         ME43 9833 9830 7367 4239 60,Roy,      IL69            9686 1536 8102 2219 165,                        5 Swallow Alley,                         88.01
1,                Charmine,      BG92 TOXX 8380 785I JKRQ JS,          Sarette,        MU67 RYRU 9293 5875 6859 7111 075X HR,          8 Sage Place,                            36.30

I made a select that gives me the number of couple and the sum value like that Query Result (SQL Fiddle)


SELECT Count(1)     AS NumberOperation, 
       emitteriban, 
       receiveriban, 
       Sum([value]) AS SumValues 
FROM   tableesperadocetransaction 
GROUP  BY emitteriban, 
          receiveriban

I need to add the name and the address to the result so I tried to make a left join like the request below and I got this Result (SQL Fiddle)


SELECT * 
FROM   (SELECT Count(1)     AS NumberOperation, 
               emitteriban  AS _EmitterIban, 
               receiveriban AS _ReceiverIban, 
               Sum([value]) AS SumValues 
        FROM   tableesperadocetransaction 
        GROUP  BY emitteriban, 
                  receiveriban) tmp_T 
       LEFT JOIN tableesperadocetransaction 
              ON tableesperadocetransaction.emitteriban = tmp_T._emitteriban 
                 AND tableesperadocetransaction.receiveriban = 
                     tmp_T._receiveriban

So how can I correct my request to get the result that I showed earlier.

PS : The Group By Name, Address Will not work in my case because my couple don't have the some address

Aggregation and duplicated line with a left join T-SQL

2 Answers