CODEWITHSUNDEEP
sqlsql-serveroracle-database
Only a Curious Mind
Only a Curious Mind

Reputation: 2857

How to get percentage of rows from database by a column

I have a table where processes are distincted by their kind. But sometimes a lot of processes of the same kind are inserted in sequence, for example:

PROCESSID | KIND
1         |  1
2         |  1
3         |  1
4         |  1
5         |  2
6         |  3

Nowadays my query get the rows ordered by ID, but imagine in the example above, a situation when there is thousands of the same kind "1" in sequence, it's a problem for me.

I would like to get the rows by percentage, for example: Take 500 rows where 10% is kind "1", 20% is kind "2", and 50% is kind "3".

My desired result is the PROCESSIDs and not the percentage of kinds in database.

How could I do this to SQL Server and Oracle?

Upvotes: 0

Views: 130

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

You can do this in SQL Server and Oracle using window/analytic functions:

select kind, count(*) as numvals,
       count(*) * 1.0 / sum(count(*)) over () as proportion
from t
group by kind;

Upvotes: 2

Related Questions