🤠 Group by, Having, Join, View, Sub query, Scala sub query, Case [SQL]

Tables in database (mysql)

• member table

id	name	height	weight	age	job_id
1	sato	170.2	65.2	60	5
2	suzuki	151.5	50.3	53	6
3	takahashi	182.1	85.1	31	8
4	tanaka	163.5	70.6	36	3
5	watanabe	157.8	55.8	62	3
6	ito	173	65.3	75	7
7	yamamoto	166.4	49.1	25	2
8	nakamura	144.1	56.9	45	4
9	kobayashi	168.7	90.1	38	7
10	kato	178.6	78.5	26	1

• job table

id	name	salary
1	doctor	1232
2	lawyer	1028
3	engineer	515
4	accountant	1024
5	pharmacist	542
6	Childminder	341
7	teacher	1050
8	bus driver	361

💎 Group By

I need to know how many members belong to each job_id

SELECT job_id, count(*) FROM members GROUP BY job_id;

job_id	count(*)
1	1
2	1
3	2
4	1
5	1
6	1
7	2
8	1

By the way, You can't use "as" to column which is used by GROUP BY

-- Error !!
SELECT job_id as 'This is job id', count(*) FROM members GROUP BY job_id;

🙄 Why? Because SELECT SQL order is like that

FROM
↓
GROUP BY
(I don't know what is 'This is job id', nobody defined it yet)
↓
SELECT
(Here define job_id "This is job id")

▼ I wrote article about SELECT ORDER

🤹‍♀️ SELECT ORDER [SQL]

Kaziu ・ Apr 3 '22

#sql #mysql #database #beginners

💎 Having

We created this record by GROUP BY

job_id	count(*)
1	1
2	1
3	2
4	1
5	1
6	1
7	2
8	1

now We need to know job_id which has two members

SELECT job_id, COUNT(*) FROM members GROUP BY job_id HAVING COUNT(*) = 2;

🤩 Tadaaam

job_id	count(*)
3	2
7	2

↑ But it is a little hard to see right? Because there is no "job name"

💎 Join

If you know job name as well, you need to integrate job table

SELECT job_id, jobs.name, COUNT(*) FROM members INNER JOIN jobs ON jobs.id=members.job_id GROUP BY job_id HAVING COUNT(*) = 2;

job_id	name	count(*)
3	engineer	2
7	teacher	2

---

---

(We will use new table from here)

• products table

id	name	category	selling_price	cost_price	registration_date
1	t-shirt	cloth	1500	500	2018-4-5
2	ballpoint pen	office	100	30	2018-6-3
3	knife	kitchen	1200	400	2018-3-30
4	y-shirt	cloth	2300	300	2018-7-23
5	copy paper	office	500	200	2018-2-19
6	pot	kitchen	5900	2000	2018-11-26
7	box cutter	office	130	50	2018-5-11
8	printer	office	9800	2800	2019-1-12

💎 View

Save SELECT SQL which we often use and we can use the same as table, like virtual table.

for example, maybe you would often search number of products of category group

SELECT category, COUNT(*) FROM Products GROUP BY category;

😭 But all the time you have to write in down, it's not weird if your fingers burn out.

in like this case, you should use VIEW!!

CREATE VIEW ProductSum (category, count_product)
AS
SELECT category, COUNT(*) FROM Products GROUP BY category;

How should you get record? it's so simple

SELECT category, count_product FROM ProductSum;

📕 Difference between View and Table

view	table
save SELECT SQL	save real records

• reduce data because view has just SELECT SQL
• flexible for changing database content

💎 Sub query

Single use version of view

view	sub query
save SELECT SQL	put SELECT SQL directly into FROM

SELECT category, count_product
FROM (SELECT category, COUNT(*) AS count_product
       FROM Products GROUP BY category)
AS ProductSum;

💎 Scala sub query

📕 What is scala?

single value like char,int,float.
Not like Array,Object

📕 For example

SELECT name, selling_price FROM Products
WHERE selling_price > (SELECT AVG(selling_price) FROM Products);

SELECT AVG(selling_price) FROM Products = 2678.75, so it is scala value.

💎 Case

We know sale product's price informations now.

▼ conditions

more than 5000	more than 1000	other
20% discount	10% discount	not change

SELECT name,
  CASE WHEN price_on_sale >= 5000 THEN price_on_sale * 0.8
       WHEN price_on_sale >= 1000 THEN price_on_sale * 0.9
       ELSE price_on_sale
  END AS "sales price"
FROM Products;

▼ result

name	sales price
t-shirt	1350
ballpoint pen	100
knife	1080
y-shirt	2070
copy paper	500
pot	4720
cutter	130
printer	7840

😎 Thank you for reading