DEV Community

Kaziu
Kaziu

Posted on

๐Ÿค  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;
Enter fullscreen mode Exit fullscreen mode
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;
Enter fullscreen mode Exit fullscreen mode

๐Ÿ™„ 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")
Enter fullscreen mode Exit fullscreen mode

โ–ผ I wrote article about SELECT ORDER

๐Ÿ’Ž 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;
Enter fullscreen mode Exit fullscreen mode

๐Ÿคฉ 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;
Enter fullscreen mode Exit fullscreen mode
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;
Enter fullscreen mode Exit fullscreen mode

๐Ÿ˜ญ 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;
Enter fullscreen mode Exit fullscreen mode

How should you get record? it's so simple

SELECT category, count_product FROM ProductSum;
Enter fullscreen mode Exit fullscreen mode

๐Ÿ“• 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;
Enter fullscreen mode Exit fullscreen mode

๐Ÿ’Ž 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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

โ–ผ 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

Discussion (0)