Skip to main content
Engineering LibreTexts

10.7.1: Aggregate functions

  • Page ID
    93705
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    \( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)

    ( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\id}{\mathrm{id}}\)

    \( \newcommand{\Span}{\mathrm{span}}\)

    \( \newcommand{\kernel}{\mathrm{null}\,}\)

    \( \newcommand{\range}{\mathrm{range}\,}\)

    \( \newcommand{\RealPart}{\mathrm{Re}}\)

    \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)

    \( \newcommand{\Argument}{\mathrm{Arg}}\)

    \( \newcommand{\norm}[1]{\| #1 \|}\)

    \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)

    \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    \( \newcommand{\vectorA}[1]{\vec{#1}}      % arrow\)

    \( \newcommand{\vectorAt}[1]{\vec{\text{#1}}}      % arrow\)

    \( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vectorC}[1]{\textbf{#1}} \)

    \( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)

    \( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)

    \( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)

    \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)

    \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)

    Aggregate functions perform a calculation on a set of values and return a single, or summary, value. Table \(\PageIndex{1}\) lists these functions.

    FUNCTION DESCRIPTION
    AVG Returns the average of all the values, or only the DISTINCT values, in the expression.
    COUNT Returns the number of non-null values in the expression. When DISTINCT is specified, COUNT finds the number of unique non-null values.
    COUNT(*) Returns the number of rows. COUNT(*) takes no parameters and cannot be used with DISTINCT.
    MAX Returns the maximum value in the expression. MAX can be used with numeric, character and datetime columns, but not with bit columns. With character columns, MAX finds the highest value in the collating sequence. MAX ignores any null values.
    MIN Returns the minimum value in the expression. MIN can be used with numeric, character and datetime columns, but not with bit columns. With character columns, MIN finds the value that is lowest in the sort sequence. MIN ignores any null values.
    SUM Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only.
    Table \(\PageIndex{1}\): A list of aggregate functions and descriptions. (Copyright; author via source)

     

    Below are examples of each of the aggregate functions listed in Table \(\PageIndex{1}\).

    Example #1:  AVG

    SELECT AVG (price) AS ‘Average Title Price’
    FROM Books

    Example #2: COUNT

    SELECT COUNT(PubID) AS ‘Number of Publishers’
    FROM Publishers

    Example #3: COUNT

    SELECT COUNT(province) AS ‘Number of Publishers’
    FROM Publishers

    Example #3: COUNT (*)

    SELECT COUNT(*)
    FROM Employees
    WHERE job_lvl = 35

    Example #4: MAX

    SELECT MAX (HireDate)
    FROM Employees

    Example #5: MIN

    SELECT MIN (price)
    FROM Books

    Example #6: SUM

    SELECT SUM(discount) AS ‘Total Discounts’
    FROM Discounts

    10.7.1: Aggregate functions is shared under a not declared license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?