Calculating Total of the row and finding average using MySQL

Question

I have two tables App and Store

• Grouped : It is the count of application that is grouped ie HasGroup =1
• NonGrouped : It is the count of application that is not grouped ie HasGroup = 0
• Grouped (%) : It is the percentage of application that is grouped ie HasGroup =1
• NonGrouped (%) : It is the percentage of application that is not grouped ie HasGroup = 0
• AverageNonGrouped (%) : It is the average of grouped application ie (Invidual NonGrouped(%) / total NonGrouped(%))*100. example for ABC it is (75/ 165)*100 = 45.45, similarly for XYZ it is (50/ 165)*100 = 30.30. Here 165 is the sum of 75+50+40

My query is as given below

SELECT 
AppName, 
SUM( CASE HasGroup  WHEN 1 THEN 1 ELSE 0 END ) AS Grouped,
SUM( CASE HasGroup WHEN 0 THEN 1 ELSE 0 END ) AS NonGrouped,
ROUND(((SUM( CASE HasGroup  WHEN 1 THEN 1 ELSE 0 END ) / 
    (SUM( CASE HasGroup  WHEN 0 THEN 1 ELSE 0 END ) + 
        SUM( CASE HasGroup WHEN 1 THEN 1 ELSE 0 END )))*100), 2) AS GroupedPercentage,
ROUND(((SUM( CASE HasGroup  WHEN 0 THEN 1 ELSE 0 END ) / 
    (SUM( CASE HasGroup  WHEN 0 THEN 1 ELSE 0 END ) + 
        SUM( CASE HasGroup WHEN 1 THEN 1 ELSE 0 END )))*100), 2) AS NonGroupedPercentage
FROM
    Apps
    INNER JOIN Store
        ON (Apps.Store = Store.Store) 
        AND AppName !='' GROUP BY AppName 

I am able to find the columns except AverageNonGrouped (%) since I don't know how to get total NonGrouped(%), Can anyone please help me on this

Answer 1

It's big query, but it does the job (i've tested on a dataset localy)

SELECT 
    AppName, 
    SUM( CASE HasGroup  WHEN 1 THEN 1 ELSE 0 END ) AS Grouped,
    SUM( CASE HasGroup WHEN 0 THEN 1 ELSE 0 END ) AS NonGrouped,
    ROUND(((SUM( CASE HasGroup  WHEN 1 THEN 1 ELSE 0 END ) / 
    (SUM( CASE HasGroup  WHEN 0 THEN 1 ELSE 0 END ) + 
        SUM( CASE HasGroup WHEN 1 THEN 1 ELSE 0 END )))*100), 2) AS GroupedPercentage,
    ROUND(((SUM( CASE HasGroup  WHEN 0 THEN 1 ELSE 0 END ) / 
    (SUM( CASE HasGroup  WHEN 0 THEN 1 ELSE 0 END ) + 
        SUM( CASE HasGroup WHEN 1 THEN 1 ELSE 0 END )))*100), 2) AS NonGroupedPercentage,

    ROUND(((SUM( CASE HasGroup  WHEN 0 THEN 1 ELSE 0 END ) / 
    (SUM( CASE HasGroup  WHEN 0 THEN 1 ELSE 0 END ) + 
        SUM( CASE HasGroup WHEN 1 THEN 1 ELSE 0 END )))*100), 2) / 

    (SELECT SUM(NonGroupedPercentage)
    FROM (
    SELECT 
        ROUND(((SUM( CASE HasGroup  WHEN 0 THEN 1 ELSE 0 END ) / 
            (SUM( CASE HasGroup  WHEN 0 THEN 1 ELSE 0 END ) + 
                SUM( CASE HasGroup WHEN 1 THEN 1 ELSE 0 END )))*100), 2) AS NonGroupedPercentage
    FROM
        apps
    INNER JOIN store
            ON (apps.Store = store.Store) 
            AND AppName !='' 
    GROUP BY AppName 
    ) AS total) *100  AS AverageNonGroupedPercentage
FROM
    apps
INNER JOIN store
    ON (apps.Store = store.Store) 
    AND AppName !='' 
GROUP BY AppName