Aggregate By Column in SQL That Results in Sample Size and % with Case When

Hi I have a table in SQL (presto) that has the following format:

uuid    group_name    transaction_id      sales       device      is_local
 xyz       control              123x         11          ios         false
 abc     treatment              124x         12      android          true
 def       control              125x         13      android         false
 ghi     treatment              126x         14          ios         false
 jkl       control              127x         15          ios          true
 mno     treatment              128x         16      android         false

how do I write a query where if there were 100 devices I can get the total sample within the group name and the percentage of each device within the group name?

I want my final table to look like this based on the device column:

attribute    control_sample    treatment_sample    score_control    score_treatment
      ios               100                  50              0.5               0.33
  android               100                  50              0.7               0.40
   tablet               100                  50              0.3               0.80
       TV               100                  50              0.6               0.20

this is the attempt originally but it did not give me the output I wanted:

device AS (
  SELECT
    device AS attribute,
    COUNT(CASE WHEN group_name = 'control' THEN 1 END) AS control_sample,
    COUNT(CASE WHEN group_name = 'treatment' THEN 1 END) AS treatment_sample,
    COUNT(CASE WHEN group_name = 'control' THEN 1 END) / (SELECT COUNT(*) FROM your_table WHERE group_name = 'control') AS score_control,
    COUNT(CASE WHEN group_name = 'treatment' THEN 1 END) / (SELECT COUNT(*) FROM your_table WHERE group_name = 'treatment') AS score_treatment
  FROM your_table
  GROUP BY device

What is the best way to do this?

If I understand your question I think you were quite close to the solution. Please note that if you divide an integer by an integer you will get an integer result, and you need decimals. I suggest the following:

SELECT
      t.device AS attribute
    , COUNT(CASE WHEN t.group_name = 'control' THEN 1 END) AS control_sample
    , COUNT(CASE WHEN t.group_name = 'treatment' THEN 1 END) AS treatment_sample
    , COUNT(CASE WHEN t.group_name = 'control' THEN 1 END) / MAX(g.group_count) AS score_control
    , COUNT(CASE WHEN t.group_name = 'treatment' THEN 1 END) / MAX(g.group_count) AS score_treatment
FROM your_table AS t
INNER JOIN (
    SELECT
          group_name
        , count(*) * 1.0 group_count -- "convert" to decimal by the * 1.0
    FROM your_table
    GROUP BY
          group_name
    ) AS g ON t.group_name = g.group_name
GROUP BY
        t.device

fiddle

I would suggest to use sum window function over the grouping results:

-- sample data
with dataset(uuid, group_name, transaction_id, sales, device, is_local) as (
    values ('xyz',       'control', '123x', 11,          'ios',         false),
        ('abc',     'treatment', '124x', 12,      'android',          true),
        ('def',       'control', '125x', 13,      'android',         false),
        ('ghi',     'treatment', '126x', 14,          'ios',         false),
        ('jkl',       'control', '127x', 15,          'ios',          true),
        ('mno',     'treatment', '128x', 16,      'android',         false)
)

-- query
select attribute,
    control_sample,
    treatment_sample,
    control_sample * 1.0 / sum(control_sample) over () score_control,
    treatment_sample * 1.0 / sum(treatment_sample) over () score_treatment
from (
    select device attribute,
        count_if(group_name = 'control') control_sample,
        count_if(group_name = 'treatment') treatment_sample
    from dataset
    group by device);

Output:

But the problem with your query was (as far as I can see) is that you got struck by another case of integer division (i.e. 999/1000 is 0 when both operands are of integer type), so you can fix it by multiplying one of operands by 1.0 - for example COUNT(CASE WHEN group_name = 'control' THEN 1 END) * 1.0/ (SELECT COUNT(*) ...) AS score_control