Sql server how to get the two columns value combination which exists only once,2 to 5 times, greater-CodePudding

I have a table of transactions with the concept of sender and receiver which can send transactions

now I want to get a result set that should include a range of number of unique senders per one (1) receiver

one (1) receiver receives from 1 person(Sender)

one (1) receiver receives from 2-5 persons(Senders)

one (1) receiver receives from 6-10 persons(Senders)

one (1) receiver receives from 11-20 persons(Senders)

one (1) receiver receives to over 20 persons(Senders)

I have tried but was not able to get required result set. Can any one please help me in this.

Regards

CodePudding user response：

This would be a mess as a comment. You didn't really provide sample data and desired output to start with (I don't accept pictures as sample). Anyway, what you are describing is a simple count():

select receiver, count(distinct sender) senderCount
from myTable
group by receiver
order by count(distinct sender);

would do it. Putting the result into buckets like 1, 2-5, ... could be done in different ways. ie:

with counts(receiver, senderCount) as
(
    select receiver, count(distinct sender)
    from myTable
    group by receiver
)
select receiver,
    case 
      when senderCount = 1 then '1'
      when senderCount > 1 and senderCount <= 5 then '2-5'
      when senderCount > 5 and senderCount <= 10 then '6-10'
      when senderCount > 10 and senderCount <= 20 then '11-20'
      when senderCount > 20 then 'Over 20'
    end as individualGroup
from counts;

CodePudding user response：

Create a (virtual) table that stores the ranges, then join it with the aggregate data:

SELECT ranges.*, COUNT(*) AS ReceiversInRange --, other aggregates
FROM (VALUES
    (1,  1,  1, 'Low risk'),
    (2,  2,  5, 'Low risk'),
    (3,  6, 10, 'Medium risk'),
    (4, 11, 20, 'Medium-high'),
    (5, 20, NULL, 'High risk')
) AS ranges(id, lb, ub, label)
INNER JOIN (
    SELECT COUNT(DISTINCT Sender) AS SenderCount --, other aggregates
    FROM t
    GROUP BY Receiver 
) AS aggdata ON SenderCount >= lb AND (SenderCount <= ub OR ub IS NULL)
GROUP BY id, lb, ub, label