Group By Clause for multiple groups-CodePudding

I have a dummy dataset as follows(for representation purpose only),

Number  Duration
1123456     65
1156789     34
116578      56
119870      34
225678      56
228765      78
229474      45
339484940   34
4477595     67
33748494    23
33748494    67
4478900     44
5589090     34

What I need to do is get the sum after group by first two numbers. Mean that I need to group by 11,22,33,44 and 55. After group by further I need to make 11 and 22 group as John, both 33 and 44 group as Mike, 55 group as Ann.

Here what I need finally,

User    Total
John    368 //sum up the all the durations which starts with  11 or 22
Mike    201 //sum up the all the durations which starts with  33 or 44
Ann      34//sum up the all the durations which starts with 55

Here is my try,

select left(Number,2) as User, 
       sum(Duration) as Total 
from user_data 
group by left(Number,2);

But I have no idea to go beyond that. Can someone show me how to do this?

CodePudding user response：

Try:

SELECT (CASE
            WHEN left(number, 2) IN ('11', '22') THEN 'John'
            WHEN left(number, 2) IN ('33', '44') THEN 'Mike'
            WHEN left(number, 2) IN ('55') THEN 'Ann'
            ELSE NULL END ) AS name,
       sum(duration)
FROM numberDuration
GROUP BY name;

Sample Data:

CREATE TABLE numberDuration
(
    number   INT,
    duration INT
);
INSERT INTO numberDuration
VALUES (1123456, 65),
       (1156789, 34),
       (116578, 56),
       (119870, 34),
       (225678, 56),
       (228765, 78),
       (229474, 45),
       (339484940, 34),
       (4477595, 67),
       (33748494, 23),
       (33748494, 67),
       (4478900, 44),
       (5589090, 34);

Result:

John,368
Mike,235
Ann,34