# Median and percentiles — g.Median(…), g.Percentile(…, 0.9)

> The middle value of a group, or any percentile of it — "half the jobs were done sooner than this", "nine in ten sooner than this" — computed by the database in the same GROUP BY as the counts and sums. Interpolated between ranks, the way a spreadsheet's PERCENTILE.INC does it, for numbers and for durations alike; the same answer from a list in memory. And the number of DIFFERENT values in a group, as `g.Select(…).Distinct().Count()`.

<!-- id: query-percentiles · area: query · stability: preview · html: https://osysharp.com/reference/query/percentiles/ -->

## Summary        {#summary}
An average hides the slow cases a team is judged on. A median and a percentile say them:

```osy title="how long a job took, per team: the middle one and the slowest tenth" test app=query-percentiles
class TeamTimes {
  public string Team;
  public int Jobs;
  public decimal? MedianHours;
  public TimeSpan? Slowest;
  public int People;
}

entity Job {
  [Required, MaxLength(20)] string Team;
  [Required, MaxLength(60)] string Who;
  decimal Hours;
  DateTime At;
  DateTime? DoneAt;
}

List<TeamTimes> ByTeam() {
  return Job.Where(j => j.DoneAt != null)
            .GroupBy(j => j.Team)
            .Select(g => new TeamTimes {
              Team = g.Key,
              Jobs = g.Count(),
              MedianHours = g.Median(j => j.Hours),
              Slowest = g.Percentile(j => j.DoneAt - j.At, 0.9),     // nine in ten were done sooner than this
              People = g.Select(j => j.Who).Distinct().Count() })      // how many different people worked them
            .OrderBy(t => t.Team)
            .ToList();
}
```

One statement: `percentile_cont(0.5)`, `percentile_cont(0.9)` and `COUNT(DISTINCT …)` beside `COUNT(*)`, grouped by team.

## Signature      {#signature}
```osy syntax
g.Median(x => value)                 // the 50th percentile
g.Percentile(x => value, 0.9)        // any percentile: a CONSTANT fraction from 0 to 1
g.Select(x => value).Distinct().Count()   // how many different values the group holds
```
`value` is a number (the answer is a `decimal?`) or a duration (`TimeSpan?`, a time between two dates included). A
group with no values answers null.

## Description    {#description}

### Interpolated, the same everywhere   {#interpolated}
A percentile sits at rank `fraction × (n − 1)` of the sorted values, and between two values it is interpolated: the
hours 1, 2, 3, 4 have a median of 2.5 and a 90th percentile of 3.7. That is PERCENTILE.INC in a spreadsheet and
`percentile_cont` in Postgres, and the in-memory engine answers the same for a `List<T>` — so a test over a list and a
report over the table agree.

### The fraction is a constant   {#constant}
`g.Percentile(x => x.Hours, 0.9)` — written in the source, between 0 and 1. A fraction outside that is refused when it
compiles; the database, the in-memory engine and a data source's plan all read the same number. Over a data source
whose provider declares `QueryFeatures.Percentiles` (Snowflake, Databricks), a group's percentile of a number is worked
out by the source, to ten decimal places ([data held somewhere else (app.DataSources)](https://osysharp.com/reference/config/data-sources/)).

### Durations stay durations   {#durations}
`g.Percentile(j => j.DoneAt - j.At, 0.9)` answers a `TimeSpan`, computed on the intervals themselves: nothing is
rounded to seconds or hours on the way.

## See also       {#see-also}
- [GroupBy (and HAVING)](https://osysharp.com/reference/query/group-by/) — grouping, and the `Where` that filters groups.
- [Days, weeks and months in a time zone — at.Bucket(TimeBucket.Month, zone)](https://osysharp.com/reference/query/time-buckets/) — group by day, week or month in a time zone.
- [A grouping of groups — GroupBy, then GroupBy again, in one statement](https://osysharp.com/reference/query/grouping-of-groups/) — a percentile of per-day counts, in one statement.