Visualize SQL | Let's use HAVING,CASE,COUNT,DOUBLE NEGATION

# Beginning
This technique is based on SQL Puzzle and Answers #11 work order
Check this book for more details of the puzzle

This quzzle is to find some project which fit some conditions.
The conditions are below ① and ②
　① step_nbr is 『0』 and step_status is 『C』
　② All step_status are 『W』 except step_nbr is 『0』in the same workorder_id

SQL is short, less than 10 lines, but its idea is interesting.
It's a very good puzzle for practicing how to use HAVING, CASE clause, COUNT function.

```SQL:SQL
CREATE TABLE Projects
(
	workorder_id CHAR(5) NOT NULL,
	step_nbr INTEGER NOT NULL CHECK (step_nbr BETWEEN 0 AND 1000),
	step_status CHAR(1) NOT NULL CHECK (step_status IN ('C', 'W')), 
     -- C is completed、W is awaiting
	PRIMARY KEY (workorder_id, step_nbr)
);

INSERT INTO Projects VALUES('AA100', 0, 'C' );
INSERT INTO Projects VALUES('AA100', 1, 'W' );
INSERT INTO Projects VALUES('AA100', 2, 'W' );
INSERT INTO Projects VALUES('AA200', 0, 'W' );
INSERT INTO Projects VALUES('AA200', 1, 'W' );
INSERT INTO Projects VALUES('AA300', 0, 'C' );
INSERT INTO Projects VALUES('AA300', 1, 'C' );
```

Let's take a blue row that is workorder_id = AA100 and step_nbr = 0

![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7e5df742-ec74-4aee-c32f-18ac0e0a8385.png)

# Answer SQL (part 1)
```SQL:SQL
SELECT workorder_id
FROM Projects AS P1
WHERE step_nbr = 0 AND step_status = 'C' --step_nbr is『0』and step_status is『C』
   AND 'W' = ALL (SELECT step_status
                  FROM Projects AS P2
                  WHERE step_nbr <> 0 AND P1.workorder_id = P2.workorder_id)
```

▼ Outpt

|workorder_id|
|---|
|AA100|

---
Condition ① is in the WHERE clause
　⇒ step_nbr is 『0』 and step_status is 『C』

Condition ② is the next part
　⇒ W = ALL (SELECT step_status FROM ・・・)　
**If step_status are all 『W』 then, ALL() returns TRUE**


■ TRUE case
W = ALL (W,W,W,W,W,W)

■ FALSE case
W = ALL (W,**C**,W,W,W,W)

If step_status **are NOT all 『W』**, ALL returns 『FALSE』

# Check the Answer SQL (part 1)
```SQL:SQL
SELECT P1.*,
	(SELECT array_agg(step_status) FROM Projects AS P2
     WHERE step_nbr <> 0 AND P1.workorder_id = P2.workorder_id) AS a1
FROM Projects AS P1
--WHERE step_nbr = 0 AND step_status = 'C'
```

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/6b8e8def-9668-4e5b-9ca0-ead893d7ad83.png)

# Check the Answer SQL (part 1)
```SQL:SQL
SELECT P1.*,
	(SELECT array_agg(step_status) FROM Projects AS P2
     WHERE step_nbr <> 0 AND P1.workorder_id = P2.workorder_id) AS a1
FROM Projects AS P1
WHERE step_nbr = 0 AND step_status = 'C' --step_nbr is『0』and step_status is『C』
```

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9dd1e61f-cb8d-88c8-bb1f-05b2ffe9dc17.png)

# Answer SQL (part 2)
```SQL:SQL
SELECT workorder_id
FROM Projects
GROUP BY workorder_id
HAVING SUM(CASE WHEN step_nbr <> 0 AND step_status = 'W' THEN 1
                WHEN step_nbr = 0  AND step_status = 'C' THEN 1
                ELSE 0 END) = COUNT(step_nbr);
```
▼ Output

|workorder_id|
|---|
|AA100|

#Check the Answer SQL (part 2)
```SQL:SQL
SELECT P1.*, 
  CASE WHEN P1.step_nbr <> 0 AND P1.step_status = 'W' THEN 1  ---- condition[1]
       WHEN P1.step_nbr = 0  AND P1.step_status = 'C' THEN 1  ---- condition[2]
       ELSE 0 END AS cnt  ---------------------------------------- condition[3]
FROM Projects AS P1
```
condition[1]：step_nbr is not 『0』and step_status is 『W』
condition[2]：step_nbr is 『0』and step_status is 『C』
condition[3]：Except condition[1] or [2] is condition[3]


![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/f2a906f0-7485-505c-7cf5-594a5a5d9204.png)



# Check the Answer SQL (part 2)
```SQL:SQL
-- right side of image
SELECT workorder_id, COUNT(*),
   SUM(CASE WHEN step_nbr <> 0 AND step_status = 'W' THEN 1
            WHEN step_nbr = 0  AND step_status = 'C' THEN 1
            ELSE 0 END) AS cnt
FROM Projects
GROUP BY workorder_id
```

![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b81b2e52-a37f-c973-e653-9861ce6236fd.png)

# Answer SQL (part 3)
```SQL:SQL
SELECT workorder_id
FROM Projects
GROUP BY workorder_id
HAVING COUNT(*)
   = COUNT(CASE WHEN step_nbr = 0 AND step_status = 'C'
                THEN 1
                ELSE NULL END)
    + COUNT(CASE WHEN step_nbr <> 0 AND step_status = 'W'
                THEN 1
                ELSE NULL END)
```
▼ Output

|workorder_id|
|---|
|AA100|

----

『part 3』 is slightly different from 『part 2』・・・ but they have the same idea.
When I saw this puzzle, I thought it was interesting to use CASE statement 
and COUNT function.

To me, 『part 2』 is nice and neat!!

Let's me say more ・・・

In 『part 2』, using like **COUNT(step_nbr)**. This `does not count if step_nbr is NULL`
In 『part 3』, using like **COUNT(*)**. This `count all rows including NULL`

I don't explain in detail here
but it is an important specification of the COUNT function.

Since CASE statement and COUNT function are often use
learning lots of use case will improve your skills.

![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9fab4bd1-8b22-b1e4-a595-b8802c1b10c4.png)

# Answer SQL (part 4)
```SQL:SQL
SELECT DISTINCT workorder_id  --there are three same data, pick one
FROM Projects P1
WHERE NOT EXISTS(
  SELECT step_status FROM Projects P2
  WHERE P1.workorder_id = P2. workorder_id
    AND step_status <> CASE WHEN step_nbr = 0
                            THEN 'C'
                            ELSE 'W' END)
```
▼ Output

|workorder_id|
|---|
|AA100|

-----

『part 4』 does not use HAVING but use DOUBLE NEGATION


In the inside frame
if step_nbr is『0』and step_status **is not 『C』**
if step_nbr is not『0』and step_status **is not 『W』**

In the outside frame
taking out data that `does not fit(NOT EXISTS)` **the inside frame conditions**
　⇒ `Deny` **the inside denial** on the outside again

try to read like below ・・・

data that `does not exist` {step_nbr is 『0』and step_status **is not 『C』**}
　⇒　those data are gone so that below data are left
　⇒　data that exist {step_nbr is 『0』and step_status is 『C』}


data that `does not exist` {step_nbr is not『0』and step_status **is not 『W』**}
　⇒　those data are gone so that below data are left
　⇒　data that exist {step_nbr is not『0』and step_status is 『W』}

Deny a negative sentence　⇒　Affirmative sentence

It takes time to get used to this idea・・・I think.　It was to me.
Search key words like "relational deivsion" or "double negation"
This technique is often use in SQL

![rep_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/26ad16f9-5b28-4d98-bd56-130e252a8193.png)

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