Groeperen en HAVING

Introductie tot SQL Server

John MacKintosh

Instructor

Eenvoudige SELECT

SELECT 
  SUM(demand_loss_mw) AS lost_demand 
FROM grid;

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

Groeperingsfout

Kunnen we dit uitsplitsen door een extra kolom toe te voegen?

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

Aggregatiefuncties gebruiken in SELECT
Data filteren met WHERE
Data opsplitsen in groepen met GROUP BY
Wat als we per groep willen sommeren?
... en daarna op die sommen filteren?

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

Samenvatting

GROUP BY splitst de data in combinaties van één of meer waarden
WHERE filtert op rijniveaus
HAVING komt na GROUP BY en filtert op groepen of aggregaten

Laten we onze skills testen!

Introductie tot SQL Server