Aggregating Data

Introduction to SQL Server

John MacKintosh

Instructor

SUM - single column

Calculate the total amount of a column value with SUM()

SELECT 
  SUM(affected_customers) AS total_affected 
FROM grid;

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

SUM - two or more columns

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

The wrong way...

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.

Use aliases

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

Average

SELECT 
  AVG(affected_customers) AS avg_affected_customers 
FROM grid;

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

Let's practice!

Introduction to SQL Server