SQL Percentile function missing?

Question

It is a mystery to me with this text book example. We have simply:

Transaction_ID (primary key),   Client_ID,  Transaction_Amount, Month

1                   1       500         1   
2                   1       1000            1
3                   1       10          2
4                   2       11          2
5                   3       300         2
6                   3       10          2
...                 ...     ...         ...

I want to calculate in SQL the mean(Transaction_Amount), std(Transaction_Amount) and the some percentile(Transaction amount) grouped by Client_ID. But is seems, even given that percentile is a very similar calculation than the standard deviation, SQL cannot do it with a simple statement as:

SELECT
    mean(Transaction_Amount),
    std(Transaction_Amount),
    percentile(Transaction_Amount) 
FROM
    myTable
GROUP BY
    Client_ID, Month

Or can it?

It gets worse becuase I also need to Group By Month in addition to Client_ID.

Thanks a lot! Sven

It depends a lot on the database. Please tag your question with the database you are using. — Gordon Linoff, Jan 15 '16 at 18:56
What Gordon Lightfoot meant was, are you using SQL Server, or Oracle, or MongoDB, or MagicPlatypus, or what? If SQL Server, at least, see http://stackoverflow.com/questions/770579/how-to-calculate-percentage-with-a-sql-statement — B. Clay Shannon-B. Crow Raven, Jan 15 '16 at 19:02
Worse yet, there is more than one type of percentile. https://en.wikipedia.org/wiki/Percentile. If the functionality of your database engine does not satisfy your business requirement, which has happened to me, you need a different plan. — Dan Bracuk, Jan 15 '16 at 19:07
I am foreced to use SAS proc sql.....trible I can tell you. And it is SAS BI Enterprise 5.1. — Sven Christian Steude, Jan 15 '16 at 19:11
Any percentile (beeing it 1% or 99%) would be good because I can then adopt the procedure to all the other that I need. — Sven Christian Steude, Jan 15 '16 at 19:12
But will double check and let you know what database it is. I think Oracle. — Sven Christian Steude, Jan 15 '16 at 19:34
it is oracle, any help appreciated with the queery for, say ths 95% percentile. @Gordon Linoff — Sven Christian Steude, Jan 16 '16 at 09:32
I am pretty sure that `percentile` takes more than one argument — Ed Heal, Jan 16 '16 at 13:07
Are you looking for the [NTILE](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions101.htm) function ? — nos, Jan 16 '16 at 13:18
First you say you use "*SAS proc SQL*", then you say it's Oracle. So what is it? — , Jan 16 '16 at 13:42

score 0 · Answer 1 · answered Jan 16 '16 at 13:05

I'm sure Oracle can do the calculations you want. I just don't know what they are. You specify that you want something grouped by ClientId. Yet, your sample query has two keys in the GROUP BY.

Some functions that you want to look at are:

AVG()
STDDEV()
PERCENT_RANK()

Without sample data and desired results (or a very clear explanation of what you are trying to calculate), I can't put together a query.

SQL Percentile function missing?

1 Answers1