T-SQL How to configure Group by so that specific values would be correctly shown-CodePudding

My current T-SQL query provides the following results:

Query:

WITH CTE AS
(
SELECT SubscriberID, sum(valueMB) as ValuesMB
FROM dbo.InternetNetwork
GROUP BY SubscriberID
),
CTE2 AS (
SELECT ab.planID, a.SubscriberID, MAX(ValuesMB) as MaximumValue
FROM CTE AS a
left join 
Subscriber as ab on a.SubscriberID= ab.SubscriberID
GROUP BY ab.planID, a.SubscriberID
)
select *
FROM CTE2 as b
ORDER BY b.MaximumValue desc

Output:

planID | SubscriberID | MaxValue
19           1555       97536.00
18           3528       97478.00
2            4029       93413.00

Query #2:

WITH CTE AS
(
SELECT SubscriberID, sum(valueMB) as ValuesMB
FROM dbo.InternetNetwork
GROUP BY SubscriberID
),
CTE2 AS(
SELECT ab.planID, MAX(ValuesMB) as MaximumValue
FROM CTE AS a
left join 
Subscriber as ab on a.SubscriberID= ab.SubscriberID
GROUP BY ab.planID
)
SELECT pl.OperatorID, MAX(b.MaximumValue) as Super
FROM CTE2 as b
left join
Plan as pl on b.planID= pl.planID
GROUP BY pl.operatorID
ORDER BY pl.operatorID

Output #2:

OperatorID | Value
1            93413.00
2            86017.00
3            97536.00

I would like to also include a subscriberID, but I'm unable to figure out a way to do so, as the only way to do it, is including in the last SELECT and adding to GROUP BY, which when done, makes a mess of a result which is not accurate.

My desired output:

 OperatorID | Value     | SubscriberID
    1         93413.00    4029
    2         86017.00    164
    3         97536.00    1544

internet network data:

SubscriberID    ValuesMB
1               28
1               27
2               27
2               27
2               27
3               29
3               28
3               27
3               27
4               27
4               27
4               29

Subscriber Data:

SubscriberID    PersonID    PlanID
1               1           3
2               2           10
3               2           6
4               3           14
5               3           1
6               4           18
7               5           5
8               5           1
9               5           9
10              5           16
11              6           13
12              6           13
13              6           20
14              6           16
15              7           4

Plan data

PlanID  OperatorID
1       1
2       1
3       2
4       2
5       2
6       2
7       2
8       2
9       2
10      2
11      2
12      3
13      3
14      3
15      3
16      3
17      3
18      3
19      3
20      3

The tables are somewhat like this related InternetNetwork-> Subscriber -> Plan. InternetNetwork contains how much each Subscribed has used. Each Subscriber has Plan associated with him. Each Plan contains a different Operator, there are only three. I wish to list all three operators, the data transferred by the subscriber of the plan that has the operator and Subscriber ID.

CodePudding user response：

Window functions allow you to have fields in your select along with aggregate functions. You can do something like this

;WITH CTE AS
(
  SELECT I.SubscriberID, 
         S.PlanID,
         SUM(ValuesMB) OVER(PARTITION BY i.SubscriberID)as ValuesMB
  FROM dbo.InternetNetwork I
  JOIN Subscriber S
      ON I.SubscriberID = S.SubscriberID
),
CTE2 AS
(
  SELECT p.operatorID,
         a.SubscriberID,     
         a.ValuesMB,
         ROW_NUMBER() OVER(PARTITION BY p.operatorID ORDER BY a.ValuesMB DESC) as rn
  FROM CTE a
  join [Plan] P
    on a.planID = P.planID
)
SELECT operatorID,
       ValuesMB,
       SubscriberID
FROM CTE2
where rn = 1