Create custom groups on columns and count another column in sql-CodePudding

I'm using sql server and I have a table named Table that looks like this.

SenderId	ReciverId	ItemCount
1	2	5
1	4	3
1	6	4
2	1	2
2	5	6
3	1	1
4	3	7
4	5	4
5	2	6
5	4	2
5	6	6
6	4	3

I want to make groups of the SenderIds and ReciverIds. Then I would like to total the ItemCount between those groups. The Groupings would be as follows.

SenderId 1 = First. SenderIds 2,3 = Second. SenderIds = 4,5,6 = Third ReciverId 1 = First. ReciverIds 2,3 = Second. ReciverIds = 4,5,6 = Third

I want to return the following

SenderGroup	ReceiverGroup	ItemCount
First	First	0
First	Second	5
First	Third	7
Second	First	3
Second	Second	0
Second	Third	6
Third	First	0
Third	Second	13
Third	Third	15

I've tried a few different queries without much success. Here's some of what I have so far.

SELECT 'First' AS SenderGroup, COUNT(ItemCount) AS ItemCount
FROM Table
WHERE SenderId IN (1)

CodePudding user response：

To get the exact result you are expecting, you can use a cte to first build the custom group and then create fetch the related data from your table joining with cte.

A query will be something like

with dummyMap AS (
  select 1 as id, 'First' as des UNION ALL 
  select 2, 'Second' UNION ALL 
  select 3, 'Second' UNION ALL 
  select 4, 'Third' UNION ALL 
  select 5, 'Third' UNION ALL 
  select 6, 'Third' 
)
select  sndrMap.des 'SenderGroup', rcvrMap.des 'ReceiverGroup', sum(isnull(ItemCount,0)) 'ItemCount'
from dummyMap sndrMap 
cross join dummyMap rcvrMap 
left join <your-table> on ReciverId = rcvrMap.id and SenderId = sndrMap.id
group by sndrMap.des, rcvrMap.des 
order by 
    case sndrMap.des when 'First' then 1 when 'Second' then 2 else 3 end asc, 
    case rcvrMap.des when 'First' then 1 when 'Second' then 2 else 3 end asc

here is a fiddle

CodePudding user response：

You may use a least/greatest trick here, then aggregate by sender and receiver and find the counts:

WITH cte AS (
    SELECT CASE WHEN SenderId < ReciverId
                THEN SenderId ELSE ReciverId END AS SenderGroup,
           CASE WHEN SenderId < ReciverId
                THEN ReciverId ELSE SenderId END AS ReceiverGroup,
           ItemCount
    FROM yourTable
)

SELECT SenderGroup, ReceiverGroup, SUM(ItemCount) AS ItemCount
FROM cte
GROUP BY SenderGroup, ReceiverGroup;

Demo