การจัดกลุ่มและ Having

SQL Server เบื้องต้น

John MacKintosh

Instructor

SELECT อย่างง่าย

SELECT 
  SUM(demand_loss_mw) AS lost_demand 
FROM grid;

+-------------+
| lost_demand | 
+-------------+
| 177888      |
+-------------+

ข้อผิดพลาดจากการจัดกลุ่ม

เพิ่มคอลัมน์อื่นเข้าไปได้ไหม?

SELECT 
  SUM(demand_loss_mw) AS lost_demand, 
  description 
FROM grid;

Msg 8120, Level 16, State 1, Line 1
Column 'grid.description' is invalid in the select list because it is not contained in 
either an aggregate function or the GROUP BY clause.

SELECT 
  SUM(demand_loss_mw) AS lost_demand,
  description 
FROM grid 
GROUP BY description;

+----------- -+-------------------------------------------------------+
| lost_demand | description                                           | 
+-------------+-------------------------------------------------------+
| NULL        | Actual Physical Attack                                |
| NULL        | Cold Weather Event                                    |
| NULL        | Cyber Event with Potential to Cause Impact            |
| 40          | Distribution Interruption                             |
| 2           | Distribution System Interruption                      |
| NULL        | Earthquake                                            |
| NULL        | Electrical Fault at Generator                         |
| 338         | Electrical System Islanding                           |
| 24514       | Electrical System Separation Islanding                |
| 15          | Electrical System Separation Islanding Severe Weather |
+-------------+-------------------------------------------------------+

SELECT 
  SUM(demand_loss_mw) AS lost_demand, 
  description 
FROM grid 
WHERE 
  description LIKE '%storm' 
  AND demand_loss_mw IS NOT NULL 
GROUP BY description;

+----------- -+--------------------------------------------+
| lost_demand | description                                | 
|-------------+--------------------------------------------|
| 996         | Ice Storm                                  |
| 420         | Load Shed Severe Weather   Lightning Storm |
| 332         | Major Storm                                |
| 3           | Severe Weather  Thunderstorm               |
| 413         | Severe Weather  Wind Storm                 |
| 4171        | Severe Weather  Winter Storm               |
| 1352        | Winter Storm                               |
+-------------+--------------------------------------------+

HAVING

ใช้ฟังก์ชัน aggregate ใน SELECT ได้
กรองข้อมูลด้วย WHERE
แบ่งข้อมูลเป็นกลุ่มด้วย GROUP BY
ถ้าต้องการรวมค่าตามกลุ่มล่ะ?
... แล้วกรองผลรวมนั้นอีกที?

SELECT 
  SUM(demand_loss_mw) AS lost_demand, 
  description 
FROM grid 
WHERE 
  description LIKE '%storm' 
  AND demand_loss_mw IS NOT NULL 
GROUP BY description;

+----------- -+--------------------------------------------+
| lost_demand | description                                | 
+-------------+--------------------------------------------+
| 996         | Ice Storm                                  |
| 420         | Load Shed Severe Weather   Lightning Storm |
| 332         | Major Storm                                |
| 3           | Severe Weather  Thunderstorm               |
| 413         | Severe Weather  Wind Storm                 |
| 4171        | Severe Weather  Winter Storm               |
| 1352        | Winter Storm                               |
+-------------+--------------------------------------------+

SELECT 
  SUM(demand_loss_mw) AS lost_demand, 
  description 
FROM grid 
WHERE 
  description LIKE '%storm' 
  AND demand_loss_mw IS NOT NULL 
GROUP BY description
HAVING SUM(demand_loss_mw) > 1000;

+----------- -+--------------------------------------------+
| lost_demand | description                                | 
|-------------+--------------------------------------------|
| 4171        | Severe Weather  Winter Storm               |
| 1352        | Winter Storm                               |
+-------------+--------------------------------------------+

สรุป

GROUP BY แบ่งข้อมูลออกเป็นกลุ่มตามค่าหนึ่งค่าหรือมากกว่า
WHERE กรองข้อมูลในระดับแถว
HAVING ใช้หลัง GROUP BY เพื่อกรองกลุ่มหรือค่า aggregate

มาฝึกกันเถอะ!

SQL Server เบื้องต้น