Exploring Data With Aggregation

• Reviewing summarized values for each column is a common first step in analyzing data
• If the data exists in a database, fastest way to aggregate is to use SQL

Common Summary Statistics

• MIN() for the minimum value of a column
• MAX() for the maximum value of a column
• AVG() for the mean or average value of a column

Example:

SELECT
    AVG(InternetUse) AS MeanInternetUse,
    MIN(InternetUse) AS MINInternet,
    MAX(InternetUse) AS MAXInternet
FROM EconomicIndicators

Filtering Summary Data with Where

SELECT 
    AVG(InternetUse) AS MeanInternetUse,
    MIN(InternetUse) AS MINInternet,
    MAX(InternetUse) AS MAXInternet
FROM EconomicIndicators
WHERE Country = 'Solomon Islands'

Subtotaling Aggregations into Groups with GROUP BY

SELECT 
    Country,
    AVG(InternetUse) AS MeanInternetUse,
    MIN(InternetUse) AS MINInternet,
    MAX(InternetUse) AS MAXInternet
FROM EconomicIndicators
GROUP BY Country

Having is the WHERE for Aggregations

We cannot use WHERE with GROUP BY for filtering on aggregated columns as this will throw an error

...
GROUP BY
WHERE MAX(InternetUse) > 100

Instead, we can use the HAVING . This is how we filter on the aggregated column after using the GROUP BY clause.

GROUP BY
HAVING MAX(InternetUse) > 100

Example:

SELECT 
    Country,
    AVG(InternetUse) AS MeanInternetUse,
    MIN(GDP) AS SmallestGDP,
    MAX(InternetUse) AS MAXInternetUse
FROM EconomicIndicators
GROUP BY Country
HAVING MAX(InternetUse) > 100

Finding and Resolving Missing Data

Detecting Missing Values

• When you have no data, the empty database field contains the word NULL
• Because NULL is not a number, it is not possible to use =,<,> to find or compare missing values
• To determine if a column contains a NULL value, use IS NULL and IS NOT NULL

Returning No NULL Values

SELECT
    Country,
    InternetUse, 
    Year
FROM EconomicIndicators
WHERE InternetUse IS NOT NULL

The above SQL query returns records where the value of InternetUse column is not NULL.

Detecting NULLs

SELECT
    Country,
    InternetUse, 
    Year
FROM EconomicIndicators
WHERE InternetUse IS NULL

The above SQL query returns records where the value of InternetUse column is NULL.

Blank is Not NULL

• A blank is not the same as a NULL value
• May show up in columns containing text
• An empty string '' can be used to find blank values
• The best way is to look for a column where the Length or LEN > 0

SELECT 
    Country, 
    GDP, 
    Year
FROM EconomicIndicators
WHERE LEN(GDP) > 0

Substituting missing data with a specific value using ISNULL

SELECT 
    GDP, 
    Country,
    ISNULL(Country, 'Unknown') AS NewCountry
FROM EconomicIndicators

The above SQL query returns a new column called NewCountry which has all the same value as the Country field but the only difference being every NULL value in that column is replaced by the Uknown keyword.

Substituting missing data with a column using ISNULL

Substituting values from one column or another with ISNULL

SELECT
    TradeGDPPercent,
    ImportGoodPercent,
    ISNULL(TradeGDPPercent,ImportGoodPercent) AS NewPercent
FROM EconomicIndicators

Substituting NULL values using COALESCE

COALESCE returns the first non-missing value.

COALESCE(value1, value2, value3,...valuen)

• If the value1 is NULL and value2 is not NULL then return value2
• If the value1 and value2 are NULL and value3 is not NULL then return value3 and so on.

SELECT
    TradeGDPPercent, 
    ImportGoodPercent
COALESCE(TradeGDPPercent, ImportGoodPercent, 'N/A') AS NewPercent
FROM EconomicIndicators

Changing column values with CASE

SELECT
    Continent
CASE WHEN 
		Continent = 'Europe' OR Continent = 'Asia' THEN 'Eurasia'
    ELSE 'Other'
    END AS NewContinent
FROM EconomicIndicators

Using CASE statements to create value groups

We are binning the data into discrete groups

SELECT
    Country,
    LifeExp,
CASE WHEN LifeExp < 30 Then 1
    WHEN LifeExp > 29 AND LifeExp < 40 THEN 2
    WHEN LifeExp > 39 AND LifeExp < 50 THEN 3
    WHEN LifeExp > 49 AND LifeExp < 60 THEN 4
    ELSE 5
    END AS LifeExpGroup
FROM EconomicIndicators
WHERE Year = 2007

Counts and Totals

Examining Totals with Counts

SELECT COUNT(*) FROM Incidents

The above SQL query returns the total number of records or rows in the Incidents table.

COUNT with DISTINCT

SELECT COUNT(DISTINCT Country) AS Countries
FROM Incidents

The above SQL query returns the count for the number of unique country value from the Country field in the Incidents table.

Count Aggregations

• GROUP BY can be used with COUNT() in the same way as the other aggregation functions such as AVG(), MIN(), MAX()
• Use the ORDER BY command to sort the values.
  • ASC will return the smallest values first (default)
  • DESC will return the largest values first

COUNT with GROUP BY

SELECT 
    COUNT(*) AS TotalRowsByCountry, Country
FROM Incidents
GROUP BY Country

It counts the rows, subtotaled by Country.

COUNT with GROUP BY and ORDER BY

SELECT 
    COUNT(*) AS TotalRowsByCountry, Country
FROM Incidents
GROUP BY Country
ORDER BY Country ASC

It counts the rows, subtotaled by Country.

Column Totals with SUM

• SUM() provides numeric total of the values in a column
• It follows the same pattern as other aggregations
• Combine it with GROUP BY to get subtotals based on columns specified

Adding Column Values

SELECT
    SUM(DurationSeconds) AS TotalDuration, Country
FROM Incidents
GROUP BY Country

It calculates the sum of the DurationSeconds column subtotaled by Country column from the Incidents table.

Math with Dates

DATEPART

• DATEPART is used to determine what part of the date you want to calculate. Some of the common abbreviations are:
  • DD for Day
  • MM for Month
  • YY for Year
  • HH for Hour

Common date functions

• DATEADD(): Add or subtract datetime values
• DATEDIFF(): Obtain the difference between two datetime values

DATEADD

To add or subtract a value to get a new data, we use DATEADD() Syntax: DATEADD(DATEPART, number,date)

• DATEPART: Unit of measurement (DD, MM, etc.)
• number: An integer value to add
• date: A datetime value

DATEDIFF

It returns a datepart value after a number has been added or substracted to a date. Syntax: DATEDIFF(datepart, startate, enddate)

• datepart: Unit of measurement (DD,MM,etc.)
• startdate: The starting date value
• enddate: An ending datetime value

SELECT
DATEDIFF(DD, '2020-05-22', '2020-06-21') AS Difference1,
DATEDIFF(DD, '2020-07-21', '2020-06-21') AS Difference2,

The above SQL query refers the difference in day between startdate and enddate provided.