(Retention) How to divide all values by value in first row across different categories of users in S-CodePudding

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
)

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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;