MySQL Case statement with multiple AND OR cannot be nested

I am trying to group the iOs versions into

11,12,13,14,14.5 and above

but having problem with the AND OR part of the CASE statement where the 14. versions supposed to be grouped.
I have tried to add brackets but then the query cannot be executed. this way it skips the
OR part of the query.

— % of iOS Users per OS Version

With os_split ("device_name", "number_of_devices")
AS
(
    -- % of Mobile Device Per User
 select 
        CASE
            WHEN "os_version" ilike '11%' THEN 'iOS 11'
            WHEN "os_version" ilike '12%' THEN 'iOS 12'
            WHEN "os_version" ilike '13%' THEN 'iOS 13'
            WHEN "os_version" like '14' 
            OR "os_version" ilike '14.0%' 
            OR "os_version" ilike '14.1%'
            OR "os_version" ilike '14.2%'
            OR "os_version" ilike '14.3%' THEN 'iOS 14'
            WHEN "os_version" ilike '14.5%' THEN 'iOS 14.5'
            ELSE "os_version"
            END
            AS "os_version",
        --"os_version",
        count("os_version") as "number_of_occurences"
 FROM temp_db.data_lake.adjust_csv_imports
 WHERE TRUE
 AND "platform" = 'mobile_app'
 AND "event_name" = 'onboarding_completed'
 AND "os_name" = 'ios'
    group by 1
   HAVING count("os_version") > 0
  
 )
 SELECT 
        a1."device_name", 
        a1."number_of_devices",
        round(a1."number_of_devices"/(SELECT SUM("number_of_devices") FROM os_split),2) Pct_To_Total
FROM os_split a1,os_split a2
    WHERE TRUE
    AND a1."number_of_devices" <= a2."number_of_devices" 
    OR (a1."number_of_devices"= a2."number_of_devices" 
        AND a1."device_name" = a2."device_name")
        group by 
        a1."number_of_devices", 
        a1."device_name"
        order by 
        a1."number_of_devices" desc, 
        a1."device_name" desc
;

What I get:

device_namenumber_of_devicesPCT_TO_TOTAL
14.4.268160.60
iOS 14.512690.11
14.68520.08
14.4.18350.07
iOS 145510.05
14.43390.03
14.4.027440.02
iOS 1323880.02
iOS 1213050.01
14.7840.00
14.6.0380.00
15.0320.00
iOS 11230.00.

What I would like to get:

device_namenumber_of_devicesPCT_TO_TOTAL
iOS 14.512000.11
iOS 14.6+8520.08
iOS 145510.05
iOS 1323880.02
iOS 1213050.01
iOS 11230.00.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I would use REGEXP inside your CASE expression to detect the various 14.x% values.

CASE
    WHEN os_version LIKE '11%' THEN 'iOS 11'
    WHEN os_version LIKE '12%' THEN 'iOS 12'
    WHEN os_version LIKE '13%' THEN 'iOS 13'
    WHEN os_version REGEXP '^14.[0-3]%' THEN 'iOS 14'
    WHEN os_version LIKE '14.5%' THEN 'iOS 14.5'
    ELSE os_version
END

It isn’t valid syntax to use OR inside a CASE expression as you are trying to do in your question.

Method 2

instead of the OR within the CASE statement which was incorrect, I just used IN()
This is definitely not the most efficient way of doing this because with each and every new iOS version I have to update the query but it does the job for now.

WITH os_split (
    "ios_version",
    "number_of_users"
) AS (
    SELECT
        CASE WHEN "os_version" ILIKE '11%' THEN
            'iOS 11'
        WHEN "os_version" ILIKE '12%' THEN
            'iOS 12'
        WHEN "os_version" ILIKE '13%' THEN
            'iOS 13'
        WHEN "os_version" in('14',
            '14.4',
            '14.4.0',
            '14.1.1',
            '14.2',
            '14.4.2',
            '14.3',
            '14.4.1',
            '14.3.0') THEN
            'iOS 14'
        WHEN "os_version" ILIKE '14.5%' THEN
            'iOS 14.5'
        ELSE
            'iOS 14.6+'
        END AS "os_version",
        --"os_version",
        count("os_version") AS "number_of_occurences"
    FROM
        temp_db.data_lake.adjust_csv_imports
    WHERE
        TRUE
        AND "platform" = 'mobile_app'
        AND "event_name" = 'onboarding_completed'
        AND "os_name" = 'ios'
    GROUP BY
        1
    HAVING
        count("os_version") > 0
)
SELECT
    "ios_version",
    "number_of_users",
    round("number_of_users" / (
            SELECT
                SUM("number_of_users")
                FROM os_split), 3) Pct_To_Total
FROM
    os_split
GROUP BY
    "number_of_users",
    "ios_version"
HAVING
    Pct_To_Total > 0
ORDER BY
    "number_of_users" DESC,
    "ios_version" DESC;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x