Using MYSQL conditional statements and variables within a query

Question

Hi just cant seem to construct the MYSQL query Im after.

Say I have a result of two columns: 1) browser name and 2) browser count.

Where it gets complicated is I want once 90% of the total count has been reached to rename all other browsers as others and mark the left over percentage accordingly.

I know I can get the total count as a variable before I begin the main statement:

SELECT @total := COUNT(id) FROM browser_table WHERE start LIKE "2010%";

Then I can group the results by browser:

SELECT browser, COUNT(id) AS visits
FROM browser_table
WHERE start LIKE "2010%"
GROUP BY browser

I know I need to whack in a case statement (and a counter variable) to sort the columns but not sure of how to implement the into the above query:

CASE 
  WHEN counter >= 0.9* @total THEN 'other'
  ELSE browser
END AS browser;

Hope that makes sense? Thanks for your time....

Answer 1

Here's one approach...

You can calculate a running total based on this answer. Example:

SET @rt := 0;
SELECT
    browser,
    visits,
    (@rt := @rt + visits) AS running_total
FROM
    (
        SELECT
            browser,
            COUNT(id) AS visits
        FROM
            browser_table
        WHERE
            start LIKE '2010%'
        GROUP BY
            browser
        ORDER BY
            visits DESC
    ) AS sq
;

Once you have that in place, you can build on that to create an 'Other' category:

SET @threshold := (SELECT COUNT(id) FROM browser_table WHERE start LIKE '2010%') * 0.90;
SET @rt := 0;
SELECT
    browser,
    SUM(visits) AS total_visits
FROM
    (
        SELECT
            IF (@rt < @threshold, browser, 'Other') AS browser,
            visits,
            (@rt := @rt + visits) AS running_total
        FROM
            (
                SELECT
                    browser,
                    COUNT(id) AS visits
                FROM
                    browser_table
                WHERE
                    start LIKE '2010%'
                GROUP BY
                    browser
            ) AS sq1
        ORDER BY
            visits DESC
    ) AS sq2
GROUP BY
    browser
ORDER BY
    total_visits DESC
;