Finding the Mean, Median, and Mode in PostgreSQL
How to find the mean, median, and mode from column data in Postgres. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - learn more at https://hashrocket.com and www.pgcasts.com
Transcript:
Hey everyone, today we're going to look at how to find the mean, median, and mode from column data in Postgres.
Mean
To get the mean, or average, value for a column, we can use the avg aggregate function. This function expects the name of the column we're trying to get the average of
```sql
select avg(age) from people;
```
Mode
To get the mode, or the most commonly occurring value in our column, we can use the ordered set aggregate function `mode()`. This aggregate function requires an ordered group to be specified in order for it to work properly. In our case, we're looking for the most common age in our group of data, so we're going to pass "within group" and then "order by age".
```sql
select mode() within group (order by age) from people;
```
One thing to note with the mode aggregate function is that if there are multiple equally occurring results, only the first is returned.
Median
To get the median value from our data set, we won't be using an aggregate function. Instead, we'll be making use of the “offset” clause. Since we're going to be using offset, it's important that we order our data by the age column, so that we're getting the true median value.
The offset clause enables us to find the median because it can accept subqueries as part of its logic. This means that we can use offset with a subquery to find the total count of entries in our table, and divide that in half to get midway through our data set.
Finally, we'll limit our response to just one, since we only care about the median value.
```sql
select age
from people
order by age
offset (select count(*) from people)/2
limit 1;
```
Thanks for watching!
Watch video Finding the Mean, Median, and Mode in PostgreSQL online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 19 September 2019, don't forget to share it with your friends and acquaintances, it has been viewed on our site 3,747 once and liked it 42 people.