{"id":824,"date":"2019-12-05T14:41:58","date_gmt":"2019-12-05T09:11:58","guid":{"rendered":"https:\/\/binaryterms.com\/?p=824"},"modified":"2022-02-14T15:14:13","modified_gmt":"2022-02-14T09:44:13","slug":"aggregate-functions-in-sql","status":"publish","type":"post","link":"https:\/\/binaryterms.com\/aggregate-functions-in-sql.html","title":{"rendered":"Aggregate Functions in SQL"},"content":{"rendered":"

Aggregate functions in SQL evaluate the set of values and return a single value as a result. SQL has five aggregate functions count, average, maximum, minimum, sum. The aggregate functions, ‘sum’ and ‘average’ operate only on numeric values. While count, maximum, minimum can also operate on non-numeric data such as string.<\/p>\n

In this section, we will discuss the steps to evaluate aggregate functions. We will discuss types of aggregate functions. We will also look into the use of aggregate function along with the ‘group by’ clause and ‘having’ clause.<\/p>\n

Content: Aggregate Function in DBMS<\/span><\/p>\n

    \n
  1. How to Aggregate Data?<\/a><\/li>\n
  2. Aggregate Functions<\/a><\/li>\n
  3. Aggregation with Group by Clause<\/a><\/li>\n
  4. Aggregation with Having Clause<\/a><\/li>\n
  5. Key Takeaways<\/a><\/li>\n<\/ol>\n

    <\/a><\/p>\n

    How to aggregate data in SQL?<\/h3>\n

    Step by step evaluation of the aggregate function in SQL.<\/p>\n

      \n
    1. The first thing evaluated in the query is the ‘from’<\/strong> clause. The ‘from’ clause evaluates the relation to be operated.<\/li>\n
    2. Next to the ‘from<\/strong> ‘clause is the ‘where’<\/strong> clause. The predicate in the ‘where’<\/strong> clause is evaluated on the outcome of the ‘from’<\/strong> clause.<\/li>\n
    3. If present, the tuples filtered by the ‘where’ clause are then grouped using the ‘group by<\/strong>‘ clause. Else all the tuples filtered by the ‘where’<\/strong> clause are considered as one group.<\/li>\n
    4. The ‘having’<\/strong> clause is then applied to each group formed by the ‘group by<\/strong>‘ clause. And the groups satisfying the ‘having’<\/strong> clause are then forwarded to the ‘select’<\/strong> clause.<\/li>\n
    5. Then the ‘select’<\/strong> clause applies the aggregate function to the evaluated groups. And generates a single result tuple<\/strong> for each group.<\/li>\n<\/ol>\n

      <\/a><\/p>\n

      Define Aggregate Functions in DBMS<\/h3>\n

      The aggregate functions are applied to the set of tuples. And as a result, they return a relation with a single attribute consisting of a single tuple value. It can also be applied to the group of ‘sets of tuples’. It results in a relation with a single attribute and one tuple for each group.<\/p>\n

      All aggregate functions avoid ‘null values’ excluding the (*) function. Count function has variations regarding Null values.<\/p>\n

      Below is the list of all aggregate functions in SQL:<\/p>\n

        \n
      1. Count (count)<\/a><\/li>\n
      2. Average (avg)<\/a><\/li>\n
      3. Maximum (max)<\/a><\/li>\n
      4. Minimum (min)<\/a><\/li>\n
      5. Total (sum)<\/a><\/li>\n<\/ol>\n

        Note<\/strong>: We can name<\/strong> the attribute of the result relation obtained by aggregation using the ‘as’<\/strong> clause. We can use the ‘as’ clause for all aggregate functions.<\/p>\n

        How to Use the Aggregate function in SQL?
        \n        <\/a><\/h4>\n

        COUNT( ) Aggregate Function<\/h4>\n

        This aggregate function counts the number of tuples in the relation. It has certain variations:<\/p>\n