CODEWITHSUNDEEP
mysqlsqlpercentagepresto
justnewbie89
justnewbie89

Reputation: 57

Percentage calculation from another column sql / presto

I have this table

month_year  shopid  payment_method   count
Jul-19      3lokpn99    CC           115
Jul-19      3lokpn99    PAYPALL      1
Jul-19      6jyhikmm    CC           180
Jul-19      6jyhikmm    PAYPALL      7
Aug-19      3lokpn99    CC           108
Aug-19      3lokpn99    PAYPALL      4
Aug-19      6jyhikmm    CC           114
Aug-19      6jyhikmm    PAYPALL      3

what im looking for is to calculate percentage for each payment method on that month. desired result is like this

month_year  shopid  payment_method   count   %
Jul-19      3lokpn99    CC           115    99.14
Jul-19      3lokpn99    PAYPALL      1      0.86
Jul-19      6jyhikmm    CC           180    96.26
Jul-19      6jyhikmm    PAYPALL      7      3.74
Aug-19      3lokpn99    CC           108    96.43
Aug-19      3lokpn99    PAYPALL      4      3.57
Aug-19      6jyhikmm    CC           114    97.4
Aug-19      6jyhikmm    PAYPALL      3      2.56

Upvotes: 1

Views: 3582

Answers (2)

Piotr Findeisen
Piotr Findeisen

Reputation: 20730

There are different approaches possible

  • using Window Functions
  • self-JOIN with aggregation (as presented in Wojciech's answer)

Window Functions approach has additional advantage that source data is read only once.

SELECT month_year, shopid, payment_method, count,
    100e0 * count / sum(count) OVER (
        PARTITION BY month_year, shopid
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
        AS "%"
FROM ...

(Tested on Presto 318)

See Presto Window Function documentation https://trino.io/docs/current/functions/window.html

Upvotes: 3

Wojciech Kołacz
Wojciech Kołacz

Reputation: 1

You could achieve this result by query:

SELECT s.*, ROUND(s.count / ss.count_summed * 100,2) AS `%`
FROM stack s
JOIN (SELECT s.month_year, s.shopid, SUM(s.count) AS count_summed FROM stack s GROUP BY s.month_year, s.shopid) ss ON ss.month_year = s.month_year AND ss.shopid = s.shopid;

Upvotes: 0

Related Questions