PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Calculating a share value group by in Postgres

I want to calculate percentage of value in Group by. My table data:

id date artist track listenings
1 2023-01-01 C track_1 2016
2 2023-01-01 C track_2 3800
3 2023-01-01 B track_3 2311
4 2023-01-01 A track_4 4180
5 2023-01-01 C track_5 2013
6 2023-01-01 A track_6 2227
7 2023-01-01 B track_7 1006
8 2023-01-01 B track_8 720
9 2023-01-01 A track_9 2438
10 2023-01-01 A track_10 2654
11 2023-01-02 C track_1 3347
12 2023-01-02 C track_2 3100
13 2023-01-02 B track_3 2436
14 2023-01-02 A track_4 4821
15 2023-01-02 C track_5 1485
16 2023-01-02 A track_6 3157
17 2023-01-02 B track_7 1993
18 2023-01-02 B track_8 2953
19 2023-01-02 A track_9 1651
20 2023-01-02 A track_10 1260

I need to calculate the share of listens to the track from listenings to all tracks of one artist for each date for each of the artists. The output table must be like

id date artist track percentage
1 2023-01-01 C track_1 0.257
2 2023-01-01 C track_2 0.486
3 2023-01-01 B track_3 0.572
4 2023-01-01 A track_4 0.364
5 2023-01-01 C track_5 0.257
11 2023-01-02 C track_1 0.422

and so on. Please help me to write this script.

Tags:

2

Answers


  1. 0

    Using a subquery to find the total number of listens per artist, per date:

    select t.*, round(cast(t.listenings as numeric)/cast((select sum(t1.listenings) 
    from tracks t1 
    where t1.artist = t.artist and t.date = t1.date) as numeric), 3) 
from tracks t

    See fiddle

    Login or Signup to reply.
  2. 0

    Just use window functions!

    select t.*,
    listenings::numeric 
    / sum(listenings) over(partition by artist, date) pct
from tracks
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search