GROUP BY und HAVING

Einführung in SQL Server

John MacKintosh

Instructor

Einfache SELECT-Abfrage

SELECT 
  SUM(demand_loss_mw) AS lost_demand 
FROM grid;

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

Gruppierungsfehler

Können wir das durch Hinzufügen einer weiteren Spalte anpassen?

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

Aggregatfunktionen in SELECT verwenden
Daten mit WHERE filtern
Daten mit GROUP BY in Gruppen aufteilen
Was, wenn wir Werte nach Gruppen zusammenfassen wollen?
... und dann nach diesen Summen filtern?

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                               |
+-------------+--------------------------------------------+

Zusammenfassung

GROUP BY teilt Daten in Kombinationen aus einem oder mehreren Werten auf
WHERE filtert nach Zeilenwerten
HAVING erscheint nach der Klausel GROUP BY und filtert nach Gruppen oder Aggregaten

Lass uns üben!

Einführung in SQL Server