Visualize SQL | Smart Use of Aggregate Functions. Nice and Neat SQL

# Beginning
This technique is based on SQL Puzzle and Answers # 13 teacher
It is better with 『SQL Puzzle and Answers』 to read this article.

This quiz is to count number of teachers who are in charge of subjects
and to display their name in line

Its format is like  below

|subject ID | student name | 1st teacher name | 2nd teacher name |

conditions are ・・・・
　[1]　Display in one row for each subject
　[2]　There are two space to display teacher's name
　　　 Number of teachers is different depending on subject
　　　　・ there is one teacher　　　　　　　　　1st teacher　　NULL
　　　　・ there are two teachers　　　　　　　　1st teacher　　2nd teacher
　　　　・ there are more than two teachers　　　1st teacher　　--More--
　[3]　Names are sorted

There are three subjects like 『subject 10』『subject 20』『subject 30』
One or more teachers are in charge of each subject
We are going to get data like below

![rep_0.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/0ceb3c8d-c5b4-1109-0518-fc58eadb88f7.png)

# Table and Data
```SQL:SQL
CREATE TABLE Register(
	course_nbr INTEGER NOT NULL,     -- subject ID
	student_name CHAR(10) NOT NULL,  -- student name
	teacher_name CHAR(10) NOT NULL   -- teacher name
);
 
INSERT INTO Register VALUES(10, 'student１', 'teacher1');
INSERT INTO Register VALUES(20, 'student１', 'teacher1');
INSERT INTO Register VALUES(20, 'student１', 'teacher2');
INSERT INTO Register VALUES(30, 'student１', 'teacher1');
INSERT INTO Register VALUES(30, 'student１', 'teacher2');
INSERT INTO Register VALUES(30, 'student１', 'teacher3');
```

# Answer SQL(part 1)
```SQL:SQL
SELECT R1.course_nbr,R1.student_name,MIN(R1.teacher_name),NULL
FROM Register AS R1
GROUP BY R1.course_nbr,R1.student_name
HAVING COUNT(*) = 1

UNION

SELECT R1.course_nbr,R1.student_name,MIN(R1.teacher_name),MAX(R1.teacher_name)
FROM Register AS R1
GROUP BY R1.course_nbr,R1.student_name
HAVING COUNT(*) = 2

UNION

SELECT R1.course_nbr,R1.student_name,MIN(R1.teacher_name),'--More--'
FROM Register AS R1
GROUP BY R1.course_nbr, R1.student_name
HAVING COUNT(*) > 2
```

▼ Output

subject 10 : Only one teacher. 2nd teacher name is empty
subject 20 : There are two teachers. Both teacher's name are displayed
subject 30 : There are three teachers. Displayed 1st teacher name and --More--

|course_nbr|student_name|teacher_name|teacher_name|
|---|---|---|---|
|10|student１|teacher1|NULL|
|20|student１|teacher1|teacher2|
|30|student１|teacher1|--More--|

This is simple SQL
After `GROUP BY course_nbr, student_name`, count row number
The number of row is the number of teachers
The teacher name to be displayed is customized according to the number of teachers.

below image
[1] left side　　 : Register table data
[2] middle　　　: Output after `GROUP BY course_nbr, student_name`
[3] right side　　: Answer SQL

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/feb2c281-4463-636b-4115-6a24b7ddbd7c.png)

What this SQL is doing is ■ 1 ～ 5

■ 1　Take out subject with one teacher
　　　⇒　HAVING COUNT(*) = 1
　　　⇒　1st name is MIN(teacher_name). 2nd teacher name is NULL

■ 2　Combine the following data using UNION

■ 3　Take out subject with two teachers
　　　⇒　HAVING COUNT(*) = 2
　　　⇒　1st name is MIN(teacher_name). 2nd teacher name is MAX(teacher_name)
　　　⇒　Names are sorted because of using MIN and MAX functions

■ 4　Combine the following data using UNION


■ 5　Take out subject with more than two teachers
　　　⇒　HAVING COUNT(*) > 2
　　　⇒　1st name is MIN(teacher_name). 2nd teacher name is --More--

caution : we get error if not using MAX or MIN functions
　　　　`column "teacher_name" must appear in the GROUP BY clause or` 
　　　　`be used in an aggregate function`

We use MAX or MIN functions because・・・
　[1] : **teacher name is not aggregate variable**
　[2] : **to sort teachers name**

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/f7ac67f8-554e-93b1-6da6-6140bd984d5b.png)

This is the end of Answer SQL(part 1)

The main subject is from here. Let's see Answer SQL(part 2)
You can get the same result in a short SQL depending on how you write the SQL

# Answer SQL(part 2)
```SQL:SQL
SELECT  course_nbr,student_name,
		MIN(teacher_name), -- 1st teacher name
        CASE COUNT(*) WHEN 1 THEN NULL
                      WHEN 2 THEN MAX(teacher_name)
                      ELSE '--More--' END  -- 2nd teacher name
FROM Register
GROUP BY course_nbr, student_name
```

▼ Output

|course_nbr|student_name|teacher_name|teacher_name|
|---|---|---|---|
|10|student１|teacher1|NULL|
|20|student１|teacher1|teacher2|
|30|student１|teacher1|--More--|


Comparing to the Answer SQL『part 1』, 『part 2』 is much shorter
I think it's a good example that makes you recognize
even if the SQL with the same output
it could be readability and easy to maintain depending on how you write


![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/af7216c6-6721-2445-7ce2-52fcb8f219e6.png)


# References 
[Joe Celko's SQL Puzzles and Answers](https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)