Aggregate Arithmetic Functions

• COUNT()

  Returns the number of items found in a group.

        COUNT([ALL] expression)
        COUNT(DISTINCT expression)
        COUNT(*)
  

  Example:

        SELECT
            COUNT(ALL country) AS count_countries_all,
            COUNT(country) AS count_countries,
            COUNT(DISTINCT country) AS distinct_countries,
            COUNT(*) AS all_voters
        FROM voters;
  

• SUM()

  Return the sum of all values from a group

        SUM([ALL] expression)
        SUM(DISTINCT expression)
  

  Example:

        SELECT
            SUM(ALL_total_votes) AS tot_votes1,
            SUM(total_votes) AS tot_votes2,
            SUM(DISTINCT total_votes) AS dist
        FROM voters
        WHERE total_votes = 153;
  

• MAX() and MIN()

  Syntax:
  MAX([ALL] expression)
  MAX(DISTINCT expression)

  Returns the maximum value in the expression

  Syntax:
  MIN([ALL] expression)
  MIN(DISTINCT expression)

  Returns the minimum value in the expression

  Example:

        SELECT
            MIN(rating) AS min_rating,
            MAX(rating) AS max_rating
        FROM ratings;
  

• AVG()

  Returns the average of the values in the group.

  Syntax:
  AVG([ALL] expression)
  AVG(DISTINCT expression)

        SELECT
            AVG(rating) AS avg_rating,
            AVG(DISTINCT rating) AS avg_dist
        FROM ratings;
  

• Grouping Data

        SELECT
            company,
            AVG(rating) AS avg_rating
        FROM ratings
        GROUP BY company;
  

---

Analytic Functions

• FIRST_VALUE()

  Syntax:
  FIRST_VALUE(numeric_expression)
  OVER([PARTITION BY column] ORDER BY column ROW_or_RANGE frame)

  Returns the first value in an ordered set. OVER clause components

  Component	Status	Description
  PARTITION by column	optional	divide the result set into partitions
  ORDER BY column	mandatory	order the result set
  ROW_or_RANGE frame	optional	set the partition limits

• LAST_VALUE()

  Syntax:
  LAST_VALUE(numeric_expression)
  OVER([PARTITION BY column] ORDER BY column ROW_or_RANGE frame)

  Returns the last value in an ordered set.

• Partition Limits

  RANGE BETWEEN start_boundary AND end_boundary
ROWS BETWEEN start_boundary AND end_boundary
  

  Boundary	Description
  UNBOUNDED PRECEDING	first row in the partition
  UNBOUNDED FOLLOWING	last row in the partition
  CURRENT ROW	current row
  PRECEDING	previous row
  FOLLOWING	next row

  Example:

        SELECT
            first_name + ' ' + last_name AS name,
            gender, 
            total_votes AS votes,
            FIRST_VALUE(total_votes)
            OVER (PARTITION BY gender ORDER BY total_votes) AS min_votes,
            LAST_VALUE(total_votes)
            OVER (PARTITION BY gender ORDER BY total_votes
                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_votes
        FROM voters;
  

• LAG() AND LEAD()

  LAG(numeric_expression) OVER ([PARTITION BY column] ORDER BY column)

  Accesses date from a previous row in the same result set.

  LEAD(numeric_expression) OVER ([PARTITION BY column] ORDER BY column)

  Accesses data from a subsequent row in the sam result set.

        SELECT
            broad_bean_origin AS bean_origin,
            rating,
            cocoa_percent,
            LAG(cocoa_percent) OVER (ORDER BY rating) AS percen_lower_rating,
            LEAD(cocoa_percent) OVER (ORDER BY rating) AS percent_higher_rating
        FROM ratings
        WHERE company = 'Felchlin'
        ORDER BY rating ASC;
  

---

Mathematical Functions

• ABS(numeric_expresison)

  • Returns the absolute value of an expression.
  • Is the non-negative value of the expression.

        SELECT
            ABS(-50.4 * 3) AS negative,
            ABS(0.0) AS zero,
            ABS(73.2 + 15 + 8.4) AS positive;
  

• SIGN(numeric_expression)

  Returns the sign of an expression, as an integer:

  • -1(negative numbers)
  • 0
  • +1(positive numbers)

        SELECT
            SIGN(-50.4*3) AS negative,
            SIGN(0.0) AS zero,
            SIGN(73.2 + 15 + 8.4) AS positive;
  

• Rounding Functions

  • CEILING(numeric_expression)
    • Returns the smallest integer greater than or equal to the expression.
  • FLOOR(numeric_expression)
    • Returns the largest integer less tha or equal to the expressino
  • ROUND(numeric_expression, length)
    • Returns a numeric value, rounded to the specified length.

  Example:

        SELECT
            CEILING(-50.39) AS ceiling_neg, -- -50
            CEILING(73.71) AS ceiling_pos; -- 74
  

        SELECT
            CEILING(-50.49) AS ceiling_neg, -- 50
            FLOOR (-50.49) AS floor_neg, -- -51
            CEILING(73.71) AS ceiling_pos, -- 74
            FLOOR(73.71) AS floor_ps -- 73
            ROUND(-50.493,1) AS round_neg, -- -50.00
            ROUND(73.715, 2) AS round_pos;
  

• Exponential functions

  • `POWER(numeric_expression, power)
    • Returns the expression raised to the specified power.
  • SQUARE(numeric_expression)
    • Returns the square of the expression.
  • SQRT(numeric_expression)
    • Returns the square root of the expression.
  • Keep in mind: the type of the expression is float or can be implicitely converted to float.

• POWER() example

        SELECT
            POWER(2,10) AS pos_num,
            POWER(-2,10) AS neg_num_even_pow,
            POWER(-2,11) AS neg_num_odd_power,
            POWER(2.5,2) AS float_num,
            POWER(2, 2.72) AS float_pow;
  

• SQUARE() example

        SELECT
            SQUARE(2) AS pos_num,
            SQUARE(-2) AS neg_num,
            SQUARE(2.5) AS float_num;
  

• SQRT() example

        SELECT
            SQRT(2) AS int_num,
            SQRT(2.76)  AS float_num;