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

[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.

[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);

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)

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)

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)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store