Question 22072 in Mysql Question posted in
The official MySQL documentation can be found here.

(Retention) How to divide all values by value in first row across different categories of users in SQL? – Mysql

I have the following table:

Day Category Count
D1 A 10
D1 B 20
D2 A 8
D2 B 10
D3 A 6
D3 B 5

I’m trying to create a percentage column by dividing the values in the third column (Count) by the value for D1 across all categories in the second column (Category; in this case 10 and 20 for A and B respectively). This should output something like:

Day Category Count Pct
D1 A 10 100%
D1 B 20 100%
D2 A 8 80%
D2 B 10 50%
D3 A 6 60%
D3 B 5 25%

The furthest I got is the code below, but I can’t figure out how to do the division by category.

    SELECT
        day,
        category,
        count,
        count/(SELECT count FROM table WHERE day = 'D1')*100 AS pct
    FROM 
        table
    ORDER BY 1
)
Tags:

3

Answers


  1. 0

    this should do what you ask:

    SELECT
        day,
        category,
        count,
        count/(SELECT count 
                 FROM table as sub 
                   WHERE day = 'D1' 
                   AND sub.category = main.category)*100 AS pct
    FROM 
        table as main

    I assumed that the denominator will always just be based on "D1", and that combinations of day-category will always be unique.

    Login or Signup to reply.
  2. 0

    This should word accurately for you:

    SELECT  
main.*,  
ROUND(((main.Count/d2.Count)*100),2) 
FROM
(SELECT *   FROM day_table d1) main
  JOIN day_table d2 ON d2.Category=main.Category AND d2.Day='D1' 
  ORDER BY 
  main.Day, 
  main.Category
    Login or Signup to reply.
  3. 0

    This is the same as Asgar’s query but with the unnecessary table derivation removed –

    SELECT
    `t1`.*,
    ROUND((`t1`.`count` / `t2`.`count`) * 100) `pct`
FROM `table` `t1`
JOIN `table` `t2`
    ON `t1`.`category` = `t2`.`category`
    AND `t2`.`day` = 'D1'
ORDER BY 1, 2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search