Grouping और Having

SQL Server परिचय

John MacKintosh

Instructor

एक सरल SELECT

SELECT 
  SUM(demand_loss_mw) AS lost_demand 
FROM grid;

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

Grouping त्रुटि

क्या हम एक अतिरिक्त कॉलम जोड़कर इसे ब्रेक कर सकते हैं?

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

SELECT में aggregate functions का उपयोग कर सकते हैं
WHERE से डेटा को फ़िल्टर करें
GROUP BY से डेटा को समूहों में बाँटें
अगर हमें समूहों के आधार पर sum चाहिए तो?
... और फिर उन्हीं sums पर फ़िल्टर करें?

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 के बाद आता है और समूहों/aggregates पर फ़िल्टर करता है

चलो अपनी स्किल्स को परखें!

SQL Server परिचय