Tổng hợp dữ liệu

Giới thiệu về SQL Server

John MacKintosh

Instructor

SUM - một cột

Tính tổng giá trị một cột với SUM()

SELECT 
  SUM(affected_customers) AS total_affected 
FROM grid;

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

SUM - hai cột trở lên

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

Cách làm sai...

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

Msg 8120, Level 16, State 1, Line 6
Cột 'grid_demand_loss_mw' không hợp lệ trong danh sách chọn vì
nó không nằm trong hàm tổng hợp hoặc mệnh đề GROUP BY.

Dùng bí danh (alias)

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

Trung bình

SELECT 
  AVG(affected_customers) AS avg_affected_customers 
FROM grid;

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

Ayo berlatih!

Giới thiệu về SQL Server