数据聚合

SQL Server 入门

John MacKintosh

Instructor

SUM——单列

使用 SUM() 计算列值总和

SELECT 
  SUM(affected_customers) AS total_affected 
FROM grid;

+----------------+
| total_affected | 
|----------------|
| 70143996       |  
+----------------+

SUM——多列

SELECT 
  SUM(affected_customers) AS total_affected 
FROM grid;

SELECT 
  SUM (affected_customers) AS total_affected, 
  SUM (demand_loss_mw) AS total_loss 
FROM grid;

+----------------+------------+
| total_affected | total_loss | 
|----------------+------------|
| 70143996       | 177888     | 
+----------------+------------+

错误示例…

SELECT 
  SUM (affected_customers) AS total_affected, 
  (demand_loss_mw) AS total_loss 
FROM grid;

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

使用别名

SELECT 
  SUM (affected_customers), 
  SUM (demand_loss_mw) 
FROM grid;

+------------------+------------------+
| (No column name) | (No column name) | 
|------------------+------------------|
| 70143996         | 177888           | 
+------------------+------------------+

SELECT 
  SUM (affected_customers) AS total_affected, 
  SUM (demand_loss_mw) AS total_loss 
FROM grid;

+----------------+------------+
| total_affected | total_loss | 
|----------------+------------|
| 70143996       | 177888     | 
+----------------+------------+

COUNT

SELECT 
  COUNT(affected_customers) AS count_affected 
FROM grid;

+----------------+
| count_affected | 
|----------------|
| 807            |  
+----------------+

COUNT DISTINCT

SELECT 
  COUNT(DISTINCT affected_customers) AS unique_count_affected 
FROM grid;

+-----------------------+
| unique_count_affected | 
|-----------------------|
| 280                   |   
+-----------------------+

MIN

SELECT 
  MIN(affected_customers) AS min_affected_customers 
FROM grid;

+------------------------+
| min_affected_customers | 
|------------------------|
| 0                      |   
+------------------------+

SELECT 
  MIN(affected_customers) AS min_affected_customers 
FROM grid 
WHERE affected_customers > 0;

+------------------------+
| min_affected_customers | 
|------------------------|
| 1                      |   
+------------------------+

MAX

SELECT 
  MAX(affected_customers) AS max_affected_customers 
FROM grid;

+------------------------+
| max_affected_customers | 
|------------------------|
| 4645572                |   
+------------------------+

平均值

SELECT 
  AVG(affected_customers) AS avg_affected_customers 
FROM grid;

+------------------------+
| avg_affected_customers | 
|------------------------|
| 86919                  |   
+------------------------+

Vamos praticar!

SQL Server 入门