Explain the aggregate functions in SQL with examples?

Answer 1

Aggregate Functions in SQL Part 1: Introducing SUM and AVGMore of this Feature• Part 2: Counting Records

• Part 3: Max and Min

Join the Discussion"Need some assistance or advice? Join us in the About Databases forum."

Mike Chapple

By their very nature, our databases contain a lot of data. In previous features, we've explored methods of extracting the specific data we're looking for using the Structured Query Language (SQL). Those methods worked great when we were seeking the proverbial needle in the haystack. We were able to answer obscure questions like "What are the last names of all customers who have purchased Siberian wool during the slow months of July and August?"

Oftentimes, we're also interested in summarizing our data to determine trends or produce top-level reports. For example, the purchasing manager may not be interested in a listing of all widget sales, but may simply want to know the number of widgets sold this month. Fortunately, SQL provides aggregate functions to assist with the summarization of large volumes of data. In this three-segment article, we'll look at functions that allow us to add and average data, count records meeting specific criteria and find the largest and smallest values in a table.

All of our queries will use the WidgetOrder table described below. Please note that this table is not normalized and I've combined several data entities into one table for the purpose of simplifying this scenario. A good relational design would likely have Products, Orders, and Customers tables at a minimum.Aggregate Functions in SQL Part 1: Introducing SUM and AVGMore of this Feature• Part 2: Counting Records

• Part 3: Max and Min

Join the Discussion"Need some assistance or advice? Join us in the About Databases forum."

Mike Chapple

By their very nature, our databases contain a lot of data. In previous features, we've explored methods of extracting the specific data we're looking for using the Structured Query Language (SQL). Those methods worked great when we were seeking the proverbial needle in the haystack. We were able to answer obscure questions like "What are the last names of all customers who have purchased Siberian wool during the slow months of July and August?"

Oftentimes, we're also interested in summarizing our data to determine trends or produce top-level reports. For example, the purchasing manager may not be interested in a listing of all widget sales, but may simply want to know the number of widgets sold this month. Fortunately, SQL provides aggregate functions to assist with the summarization of large volumes of data. In this three-segment article, we'll look at functions that allow us to add and average data, count records meeting specific criteria and find the largest and smallest values in a table.

All of our queries will use the WidgetOrder table described below. Please note that this table is not normalized and I've combined several data entities into one table for the purpose of simplifying this scenario. A good relational design would likely have Products, Orders, and Customers tables at a minimum.Aggregate Functions in SQL Part 1: Introducing SUM and AVGMore of this Feature• Part 2: Counting Records

• Part 3: Max and Min

Join the Discussion"Need some assistance or advice? Join us in the About Databases forum."

Mike Chapple

By their very nature, our databases contain a lot of data. In previous features, we've explored methods of extracting the specific data we're looking for using the Structured Query Language (SQL). Those methods worked great when we were seeking the proverbial needle in the haystack. We were able to answer obscure questions like "What are the last names of all customers who have purchased Siberian wool during the slow months of July and August?"

Oftentimes, we're also interested in summarizing our data to determine trends or produce top-level reports. For example, the purchasing manager may not be interested in a listing of all widget sales, but may simply want to know the number of widgets sold this month. Fortunately, SQL provides aggregate functions to assist with the summarization of large volumes of data. In this three-segment article, we'll look at functions that allow us to add and average data, count records meeting specific criteria and find the largest and smallest values in a table.

All of our queries will use the WidgetOrder table described below. Please note that this table is not normalized and I've combined several data entities into one table for the purpose of simplifying this scenario. A good relational design would likely have Products, Orders, and Customers tables at a minimum.

Sourced From:

about.com

http://databases.about.com/od/sql/l/aaaggregate1.htm

By their very nature, our databases contain a lot of data. In previous features, we've explored methods of extracting the specific data we're looking for using the Structured Query Language (SQL). Those methods worked great when we were seeking the proverbial needle in the haystack. We were able to answer obscure questions like "What are the last names of all customers who have purchased Siberian wool during the slow months of July and August?"

Oftentimes, we're also interested in summarizing our data to determine trends or produce top-level reports. For example, the purchasing manager may not be interested in a listing of all widget sales, but may simply want to know the number of widgets sold this month. Fortunately, SQL provides aggregate functions to assist with the summarization of large volumes of data. In this three-segment article, we'll look at functions that allow us to add and average data, count records meeting specific criteria and find the largest and smallest values in a table.

All of our queries will use the WidgetOrder table described below. Please note that this table is not normalized and I've combined several data entities into one table for the purpose of simplifying this scenario. A good relational design would likely have Products, Orders, and Customers tables at a minimum.