All Sum Results under a single group by function as static conditions-CodePudding

I'm trying to get the output as follows but don't understand how can I use all sum results under a single group by function as static conditions

Sum_Total_usage	Total_User
0-100	60
101-300	40
301-500	20

**Following Static Conditional value will be the sum of FREE_UPLOAD_OCTETS & FREE_DOWNLOAD_OCTETS columns and also under conditional SESSION_START_TIME

Between 0 AND 100
Between 101 AND 300
Between 301 AND 500
Between 501 AND 800
Between 801 AND Above

Below Output is getting right now without condition:

Below is my table data:

Below is my Query:

SELECT COUNT(usd.user_name) AS All_User,
ROUND((ROUND((SUM(usd.FREE_UPLOAD_OCTETS)/1048576)))/1024,2)   ROUND((ROUND((SUM(usd.FREE_DOWNLOAD_OCTETS)/1048576)))/1024,2) AS Total_Usage
FROM user_session_detail usd
WHERE usd.SESSION_START_TIME > '2022-02-01 00.00.01' AND usd.SESSION_START_TIME < '2022-02-01 03.59.59'

CodePudding user response：

you can use group by case when condition < 101 then 1 when condition < 301 then 2 when condition < 501 then 3 when condition < 801 then 4 else 5 end at the end

CodePudding user response：

Something like this should work

Select conditions, sum(total_users) as users, sum(total_usage) as usage from(

SELECT total_users, total_usage,
case
when total_usage < 100 then 'less then 100'
when total_usage > 100 then 'more then 100'
else null
end as conditions
from user_session_detail
) b group by conditions

CodePudding user response：

None server can return the data which is absent on it. If the conditions values are absent then they must be created as a rowset. In CTE/subquery (dynamically, SELECT 100 condition UNION SELECT 300 UNION ...), or in specially created static table - it doesn't matter.

Schematically:

WITH 
userdata AS ( {the aggregate query which calculates amount per user} ),
conditions AS ( SELECT 0 AS min_value, 100 AS max_value UNION ALL
                SELECT 101, 300 UNION ALL
                ... 
                SELECT 801, 2147483647 )
SELECT CONCAT('Between ', conditions.min_value, ' and ', conditions.max_value) AS user_group,
       userdata.*
FROM userdata
JOIN conditions ON userdata.aggregated_value BETWEEN conditions.min_value AND conditions.max_value;

If MySQL version is 5.x and does not support CTEs then use subqueries.

PS. Pay attention - there is no restriction for the groups ranges in the conditions, they may be non-adjacent and/or overlap. In this case the user statistic will be categorized with all matched groups.

PPS. 2147483647 is maxvalue for INTEGER SIGNED. If the statistic have another datatype then adjust this constant value.

UPDATE

Based on these conditions i will get the user_count. So my output will be showing the range in a row and the corresponding user_count. – RaceTech

WITH 
userdata AS ( {the aggregate query which calculates amount per user} ),
conditions AS ( SELECT 0 AS min_value, 100 AS max_value UNION ALL
                SELECT 101, 300 UNION ALL
                ... 
                SELECT 801, 2147483647 )
SELECT CONCAT('Between ', conditions.min_value, ' and ', conditions.max_value) AS user_group,
       COUNT(*) AS users_in_group
FROM userdata
JOIN conditions ON userdata.aggregated_value BETWEEN conditions.min_value AND conditions.max_value
GROUP BY user_group;