Grouping and Having

Introdução ao SQL Server

John MacKintosh

Instructor

Um SELECT simples

SELECT 
  SUM(demand_loss_mw) AS lost_demand 
FROM grid;

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

Erro de agrupamento

Dá para detalhar adicionando outra coluna?

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

Use funções agregadas no SELECT
Filtre dados com WHERE
Separe em grupos com GROUP BY
E se quisermos somar por grupo?
... e depois filtrar essas somas?

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

Resumo

GROUP BY divide os dados em combinações de um ou mais valores
WHERE filtra por valores de linha
HAVING vem após GROUP BY e filtra grupos ou agregações

Vamos praticar!

Introdução ao SQL Server