MySQL 8.0.22 | Select Function(Sum, Avg, Count, Max, Min)

Hey guys! Welcome to another MySQL session with me!
Today we’re going to be talking about the functions. On my last post, I showed you how to use one of the function “count” with group by clause. If you missed it please check it out!

MySQL 8.0.22 | Select Group By + Having, Count, As

[Function]

You can use the functions only with the select statement. With the function you can get the numbers you want very easily.

It’s the basic query of using function. There are more functions than these, but I put only 5 of them that I’m going to talk about today. In this basic query, you can add where clause or group by or as… etc.

I already talked about the function ‘count’, so if you missed it please check it out now!

MySQL 8.0.22 | Select Group By + Having, Count, As

Sum is gonna add all the value in the column you chose, and Avg is gonna get you the average. And Max and Min mean Maximum and Minimum, So with them you can get the highest or the lowest value in one column.

It’ll be better to understand when you see the examples in the practice questions!

[Practice Questions]

Use the query down below, let’s do some basic function practice.

create database shop;
use shop;
create table member(
id int primary key,
name varchar(10),
city varchar(10),
grade varchar(7),
point int
);
insert member values
(1, 'Nina', 'newyork', 'diamond', 550),
(2, 'Tom', 'newyork', 'silver', 320),
(3, 'Amy', 'miami', 'bronze', 50),
(4, 'Jake', 'newyork', 'bronze', 100),
(5, 'Lisa', 'miami', 'silver', 240),
(6, 'Nicky', 'chicago', 'bronze', 100),
(7, 'Jay', 'newyork', 'gold', 490),
(8, 'Alina', 'miami', 'gold', 400),
(9, 'Karl', 'newyork', 'silver', 390),
(10, 'Julian', 'chicago', 'bronze', 180);

Q1. How much is the point total of people who lives in New York?(sum)

mysql> select city, sum(point)
    -> from member
    -> where city="newyork";
+---------+------------+
| city    | sum(point) |
+---------+------------+
| newyork |       1850 |
+---------+------------+
1 row in set (0.00 sec)

Q2. How much is the point average?(avg)

mysql> select avg(point) as Average
    -> from member;
+----------+
| Average  |
+----------+
| 282.0000 |
+----------+
1 row in set (0.00 sec)

I put as so I can put a nickname ‘Average’ for avg(point) .

Q3. How many people are living in Chicago?(count)

mysql> select city, count(*)
    -> from member
    -> where city="chicago" ;
+---------+----------+
| city    | count(*) |
+---------+----------+
| chicago |        2 |
+---------+----------+
1 row in set (0.00 sec)

Like I said on my last post, the * in the parentheses means you’re going to count the whole row. The result will be same whatever column you put in the ( ) .

Q4. What’s the Highest point among the people who lives in Miami?(max)

mysql> select city, max(point)
    -> from member
    -> where city="miami";
+-------+------------+
| city  | max(point) |
+-------+------------+
| miami |        400 |
+-------+------------+
1 row in set (0.00 sec)

Q5. What’s the Lowest point in Silver Grade?(min)

mysql> select grade, min(point)
    -> from member
    -> where grade="silver";
+--------+------------+
| grade  | min(point) |
+--------+------------+
| silver |        240 |
+--------+------------+
1 row in set (0.00 sec)

That’s all for today! Thank you for reading guys! See you on the next post!