Aggregating Data

• SUM

  It is used to calculate the total amount of a column.

  • Example:

    •   SELECT 
            SUM(affected_customers) AS total_affected
        FROM grid;
      

      Returns the total of all the affected_customers

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

      Returns the total of all the affected_customers amd demand_loss_mw columns.

• COUNT

  It returns the total count of the records

  • Example:

    •   SELECT
            COUNT(affected_customers) AS count_affected
        FROM grid;
      

• COUNT DISTINCT

  It returns the total count of the distinct records

  • Example:

    •   SELECT
            COUNT(DISTINCT affected_customers) AS unique_count_affected
        FROM grid;
      

• MIN

  It returns the minimum value of a column

  • Example:

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

      It returns the minimum value of the affected_customer column from all the records where affected_customer > 0.

• MAX

  It returns the maximum value of a column

  • Example:

    •   SELECT
            MAX(affected_customers) AS max_affected_customers
        FROM grid
        WHERE affected_customers > 0;
      

      It returns the maximum value of the affected_customer column from all the records where affected_customer > 0.

• AVG

  It returns the average value of a column

  • Example:

    •   SELECT
            AVG(affected_customers) AS avg_affected_customers
        FROM grid;
      

      It returns the average value of the affected_customer column.

• LEN

  It returns the length of a string

  • Example:

    •   SELECT
            description,
            LEN(description) AS description_length
        FROM grid;
      

• LEFT

  It returns the specified no of characters from the string from the left side.

  • Example:

    •   SELECT
            description,
            LEFT(description, 20) AS first_20_left
        FROM grid;
      

• RIGHT

  It returns the specified no of characters from the string from the right side.

  • Example:

    •   SELECT
            description,
            RIGHT(description, 20) AS last_20_
        FROM grid;
      

• CHARINDEX

  The CHARINDEX() function searches for a substring from the left-side in a string, and returns the position. If the substring is not found, this function returns 0.

  • Example:

    •   SELECT
            CHARINDEX('-', url) AS char_location,
            url
        FROM courses;
      

• SUBSTRING

  It returns the part of the string from the specified starting location to the specified end location.

  Syntax: REPLACE(string, start, end)

  • Example:

    •   SELECT
            SUBSTRING(url, 12, 12) AS target_section,
            url
        FROM courses;
      

• REPLACE

  IT replaces all occurrences of a substring within a string, with a new substring.

  Syntax: REPLACE(string, old_string, new_string)

  • Example:

    •   SELECT
            TOP(5) REPLACE(url, '_','-') AS replace_with_hyphen
        FROM grid;
      

• GROUP BY

  The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”. The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

  • Example:

    •   SELECT
            SUM(demand_loss_mw) AS lost_demand,
            description
        FROM grid
        GROUP BY description;
      

      The above SQL statement groups the records by description and for each group calculates the sum of demand_loss_mw.

    •   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;
      

      The above SQL statement groups the records by description and filters the records with the conditions specified in WHERE clause and then aggregates the demand_loss_mw field using the SUM function from the result queires.

• HAVING

  The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions. It can be used when filtering with groups. We normally use WHERE to filter data but what if we want to sum values based on groups and then filter on those groups? In such cases, we use HAVING.

  • Example:

    •   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;
      

      The above SQL statement groups the records by description and filters the records with the conditions specified in WHERE clause and then aggregates using the SUM function from the result queires. Then the results after applying aggregating functions are filterd using the conditions specified in the Having clause.

  • GROUP BY splits the data up into combination sof one or more values.
  • WHERE filters on row values
  • HAVING appears after the GROUP BY claues and filters on groups or aggregates