CODEWITHSUNDEEP
sqlpostgresql
enriqueqs
enriqueqs

Reputation: 59

percentage based on two columns

So, I have this table:

| model | user_type  | count |
+-------+------------+-------+
| 2017  | member     | 42    |
| 2017  | occasional | 562   |
| 2017  | visitor    | 156   |
| 2018  | member     | 223   |
| 2018  | occasional | 2828  |
| 2018  | visitor    | 781   |
| 2019  | member     | 185   |
| 2019  | occasional | 2193  |
| 2019  | visitor    | 596   |
| 2020  | member     | 41    |
| 2020  | occasional | 244   |
| 2020  | visitor    | 54    |

and I would like to get this table:

| model | user_type  | count | percentage    |
|-------|------------|-------|---------------|
| 2017  | member     | 42    | 0.05526315789 |
| 2017  | occasional | 562   | 0.7394736842  |
| 2017  | visitor    | 156   | 0.2052631579  |
| 2018  | member     | 223   | 0.05819415449 |
| 2018  | occasional | 2828  | 0.7379958246  |
| 2018  | visitor    | 781   | 0.2038100209  |
| 2019  | member     | 185   | 0.06220578346 |
| 2019  | occasional | 2193  | 0.7373907196  |
| 2019  | visitor    | 596   | 0.200403497   |
| 2020  | member     | 41    | 0.1209439528  |
| 2020  | occasional | 244   | 0.7197640118  |
| 2020  | visitor    | 54    | 0.1592920354  |

Where the percentage is obtained by dividing count by the sum of count for a given year (the percentage on first row is: 42/(42+562+156) )

Upvotes: 0

Views: 57

Answers (3)

Equinox
Equinox

Reputation: 6758

You can use window functions.

SELECT MODEL,user_type,count,
COUNT/SUM(COUNT) OVER(PARTITION BY MODEL ORDER BY MODEL)  AS percentage
FROM K1

Link to Fiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Simply use window functions:

select t.*,
       count * 1.0 / sum(count) over (partition by year) as ratio
from t;

The * 1.0 is just to convert the integer to a decimal of some sort for the division.

Upvotes: 2

nrainer
nrainer

Reputation: 2633

You just need to include a subquery.

SELECT
  model,
  user_type,
  count,
  count / (SELECT SUM(t2.count) FROM user_table t2 WHERE t1.model = t2.model) AS percentage
FROM user_table t1

Upvotes: 0

Related Questions