Aggregation and duplicate row with left T-SQL join
I have data you can see below based on 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 below
- Calculate the number of operations performed by multiple EmitterIBAN and ReceiverIBAN.
- Calculate the sum values for each EmitterIBAN and ReceiverIBAN pair
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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 selection which gives me the number of pairs and the value of the sum like 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 name and address to the result, so I tried to make a left join like the query 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 fix my query to get the result I showed earlier.
PS: Group by name, address won't work in my case, because my couple doesn't have an address
+3
source to share
2 answers
You can simply add emitter and address columns to the first select statement (and in the GROUP BY clause):
select count(1) as NumberOperation,
Emitter,
ReceiverAddress,
EmitterIban,
ReceiverIban,
SUM([Value]) as SumValues
FROM TableEsperadoceTransaction
Group By EmitterIban,
ReceiverIban,
Emitter,
ReceiverAddress
+2
source to share
You only add DISTINCT
SELECT DISTINCT *
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
+2
source to share