🤠 Group by, Having, Join, View, Sub query, Scala sub query, Case [SQL]
Tags: sql, mysql, database, beginners
Published: 2022-04-03 06:41:28

### 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**

```sql
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
```sql
-- 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
{% link <https://dev.to/kaziusan/sql-select-order-3mi6> %}

## 💎 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**
```sql
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

```sql
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
```sql
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!!**

```sql
CREATE VIEW ProductSum (category, count_product)
AS
SELECT category, COUNT(*) FROM Products GROUP BY category;
```
How should you get record? it's so simple
```sql
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|

```sql
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
```sql
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|

```sql
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