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

[Group By]

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

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)

--

--

--

Learn something new everyday.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Turn Your Excel Workbook Into A SQLite Database

Amazon’s 2020 cloud strategy and my 5 takeaways from AWS re:Invent 2019

Learn How an Open-Source Microservice Component Has Supported Double 11 for the Past 10 Years

Learning how to code was hard, you just forgot

Understand 5 Scopes of Pytest Fixtures

Technological malpractice in organizations

Inplace Rotate Square Matrix

What is Kubernetes, and why should I care?

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
Student Kim

Student Kim

Learn something new everyday.

More from Medium

Run code-server with self-signed public or private IP address and nginx https support

BattleBorn Beekinis — Empowering All Women

Productionizing dbt on Airflow+Kubernetes

REIMAGINING THE FUTURE OF EVENTS — FROM LIVE TO VIRTUAL TO HYBRID