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

Group By — Group tuples that have same value in certain columns. You can specify the group with having clause.

Hey guys! It’s another day for some more select clause! Today, it’s gonna be the “Group By” clause. It’s not gonna be easy to be honest, so let’s put more effort today!

[Group By]

The basic way to use group by is
select select_list from tableName group by columnName;
Of course you can put where as well, but you need to put where before the group by. And If you want to select more detailed group, you can put having after the group by . If you’re not familiar with where clause, checkout my last post down below.

MySQL 8.0.22 | How to Insert or Select Data in the Table + Where Clause

It’s might sound like they’re doing the same jobs, but they’re quite different. It’s easy when you think where is a condition for tuples, and having is a condition for group. Let’s try some practice to understand it better.

[Exercise Question]

create database shop;
use shop;
create table member(
id int primary key,
name varchar(8),
city varchar(7),
point int
);
insert into member values
(1,'Nina','seoul',100),
(2,'Tom','seoul',200),
(3,'Amy','busan',987),
(4,'Jake','naju',800),
(5,'Lisa','incheon',500),
(6,'Kiki','seoul',50),
(7,'Toto','seoul',654),
(8,'Evy','busan',300),
(9,'Jill','naju',880),
(10,'Louise','incheon',87);

There is a shop that have their customers information such as id, name, city and point. As always, you can just copy and paste the query above, but try to solve the questions on your own.

Q1. How many people live in each city? Select each city’s name and the number of people.(Count)

Before solve this question, you need to know that their’s a function for counting rows. To return the number, we can write select count(columnName) from tableName;

Answer :
mysql> select city, count(*)
    -> from member
    -> group by city;
+---------+----------+
| city    | count(*) |
+---------+----------+
| seoul   |        4 |
| busan   |        2 |
| naju    |        2 |
| incheon |        2 |
+---------+----------+
4 rows in set (0.00 sec)

I put * in the count( ) , means i’m selecting the whole row. But it’s okay to put any columns you want, cause the results will be same whether we count rows or ids.

Q2. Select the number of people in each city, and city’s name.(But, Only select the cities that have less than 3 people.)(As)

Answer :
mysql> select city, count(*) as Total
    -> from member
    -> group by city
    -> having Total<3;
+---------+-------+
| city    | Total |
+---------+-------+
| busan   |     2 |
| naju    |     2 |
| incheon |     2 |
+---------+-------+
3 rows in set (0.00 sec)

It’s not necessary, but I put as Total after count(*). It means I’m gonna return the count(*) into Total like a nickname on the select table. And then, I put having Total<3(which is same with having count(*)<3) to put condition for the group. Which means t doesn’t work if you put Total<3 in where clause. Because it’s group’s condition. Then let’s use where in the next question.

Q3. Select the number of people in each city, and city’s name.(But, Only count the people who have 500 or more point.)

Answer : 
mysql> select city, count(*)
    -> from member
    -> where point>=500
    -> group by city;
+---------+----------+
| city    | count(*) |
+---------+----------+
| busan   |        1 |
| naju    |        2 |
| incheon |        1 |
| seoul   |        1 |
+---------+----------+
4 rows in set (0.00 sec)

Now you see the difference between having and where, right?

Great! It was so much of the new stuff, but we did it anyway! I hope you all followed well. On the next sesh, I’m gonna talk about more functions like the count( ). Thanks guys! bye!