By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 10.6.7-MariaDB |
SELECT SUBSTRING_INDEX('NAME_01', '_', 1);
| SUBSTRING_INDEX('NAME_01', '_', 1) |
|---|
| NAME |
CREATE TABLE usertable ( id int, username varchar(10), status varchar(10) );
INSERT INTO usertable VALUES
(1, 'John' , 'Active')
, (2, 'Mike' , 'Active')
, (3, 'Ann' , 'Deactive')
, (4, 'Leta' , 'Active')
, (5, 'Lena' , 'Active')
, (6, 'Lara' , 'Active')
, (7, 'Mitch' , 'Active')
;
CREATE TABLE revenuetb ( subuser varchar(10), hour varchar(20), revenue int );
INSERT INTO revenuetb VALUES
('John_01' , '2/26/2022 5:00' , 5)
, ('Mike_01' , '2/26/2022 7:00' , 8)
, ('Mike_01' , '2/26/2022 7:00' , 22)
, ('Leta_03' , '2/26/2022 7:00' , 67)
, ('Leta_07' , '2/26/2022 9:00' , 56)
, ('Mitch_07' , '2/26/2022 11:00' , 34)
;
-- Original SQL
SELECT u.Username
, COALESCE(SUM(Revenue), 0) AS TOTAL_USAGE
FROM usertable u
LEFT JOIN revenuetb e
ON SUBSTRING_INDEX(e.subuser, '_', 1) = u.Username
AND e.Hour BETWEEN '1' and '9'
WHERE u.STATUS='Active'
GROUP BY u.Username
ORDER BY u.ID
;
| Username | TOTAL_USAGE |
|---|---|
| John | 5 |
| Mike | 30 |
| Leta | 123 |
| Lena | 0 |
| Lara | 0 |
| Mitch | 34 |
-- Updated SQL
SELECT username, SUM(MAX_USAGE) AS TOTAL_USAGE
FROM (
SELECT u.Username, e.hour
, COALESCE(MAX(Revenue), 0) AS MAX_USAGE
FROM usertable u
LEFT JOIN revenuetb e
ON SUBSTRING_INDEX(e.subuser, '_', 1) = u.Username
AND e.Hour BETWEEN '1' and '9'
WHERE u.STATUS='Active'
GROUP BY u.Username, e.hour
) AS derived
GROUP BY Username
;
| username | TOTAL_USAGE |
|---|---|
| John | 5 |
| Lara | 0 |
| Lena | 0 |
| Leta | 123 |
| Mike | 22 |
| Mitch | 34 |