CODEWITHSUNDEEP
sqlpostgresqlpercentagewindow-functions
user3195987
user3195987

Reputation: 15

Find percentage of first row within the same column

I have a table that looks like this: 

Day | Count  
----+------
1   | 59547  
2   | 40448  
3   | 36707  
4   | 34492

And I want it to query a result set like this: 

Day | Count | Percentage of 1st row  
----+-------+----------------------
1   | 59547 | 1  
2   | 40448 | .6793  
3   | 36707 | .6164  
4   | 34492 | .5792

I've tried using window functions, but can only seem to get a percentage of the total, which looks like this: 

Day | Count | Percentage of 1st row  
----+-------+----------------------
1   | 59547 | 0.347833452  
2   | 40448 | 0.236269963  
3   | 36707 | 0.214417561  
4   | 34492 | 0.201479024

But I want a percentage of the first row. I know I can use a cross join, that queries just for "Day 1" but that seems to take a long time. I was wondering if there was a way to write a window function to do this.

Upvotes: 0

Views: 254

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656744

Judging from your numbers, you may be looking for this:

SELECT *, round(ct::numeric/first_value(ct) OVER (ORDER BY day), 4) AS pct
FROM   tbl;

"A percentage for each row, calculated as ct divided by ct of the first row as defined by the smallest day number."

The key is the window function first_value().

-> SQLfiddle demo.

Upvotes: 2

Related Questions