MySQL’s aggregate function is used to perform calculations on multiple values and return the result in a single value like the average of all values, the sum of all values, and maximum & minimum value among certain groups of values.

Aggregate functions in MySQL are used to perform calculations on sets of values and return a single value as a result. These functions are often used with the SELECT statement to summarize or perform calculations on data in a database table.

Aggregate functions like SUM(), MIN(), MAX(), AVG(), COUNT() etc. These basically helps to perform calculations over multiple data in an attribute and returns a single value.

  • SUM – This function helps to sum of all row’s records which present into table. MySQL SUM function is used to find out the sum of a field in various records. Calculates the sum of numeric values in a column. This function basically helps to sum of all values present inside a column (attribute).

Syntax: simple SUM()

SELECT SUM(column_name) FROM table_name;

SUM() with WHERE clause.

SELECT SUM(column_name) FROM table_name WHERE condition;

For understand this see below image example

Let’s consider a table:

Let’s apply SUM() into above table:

COUNT – The COUNT () function returns the number of records returned by a select query. But remember, NULL values are not counted. This function basically helps to count number of rows in a table (relation). Let’s understand by below image:

MAX –

  • it returns maximum number in a particular filed of a table. In SQL, the MAX() function is used to retrieve the maximum value from a specified column or expression.

Syntax:

SELECT MAX(column_name) FROM table_name;

Let’s understood this example through below image:

MIN – it returns minimum number in a particular filed of a table. The MIN() function in SQL is used to retrieve the minimum value from a specified column or expression within a table. It is useful in various scenarios where you need to find the smallest value in a dataset. Lets understood this example through below image:

AVG – it returns average of numbers of a particular filed in a table. The AVG() function in SQL is used to calculate the average (mean) value of a specified column or expression within a table. It serves various purposes and can be very useful in different scenarios. Lets understood this example through below image:

Here is a small note for these above aggregate functions.

The SQL functions SUM(), MIN(), MAX(), AVG(), and COUNT() are essential tools for data analysis and reporting within a relational database management system. They serve various purposes and are used for different tasks. The real reasons for using these functions can be summarized as follows:

  1. SUM() Function:

Real Reason: The SUM() function calculates the total sum of numeric values in a column. It is used to aggregate and understand the total of a certain quantity, such as sales, revenue, quantities, or scores.

  1. MIN() Function:

Real Reason: The MIN() function finds the smallest value in a column. It helps identify the lowest value, which can be crucial for determining starting points, thresholds, or detecting anomalies.

  1. MAX() Function:

Real Reason: The MAX() function identifies the largest value in a column. It is essential for determining the maximum value within a dataset, setting limits, and identifying outliers.

  1. AVG() Function:

Real Reason: The AVG() function calculates the average (mean) value of a column. It provides insight into the central tendency of the data, allowing for better understanding and comparison of data subsets.

  • COUNT() Function:

Real Reason: The COUNT() function counts the number of rows or occurrences in a column. It is used to quantify data, count occurrences, and perform basic data validation.

In summary, the primary reasons for using these functions are:

  • Data Aggregation: These functions are used to aggregate and summarize data to provide insights into overall trends and patterns.
  • Statistical Analysis: The functions support basic statistical analysis by providing measures like sum, average, minimum, and maximum values.
  • Data Quality: They help in identifying outliers, anomalies, and data quality issues by calculating extreme values and aggregates.
  • Reporting: These functions are crucial for generating reports and presenting data in a more meaningful and understandable manner.
  • Decision-Making: By calculating these values, you can make informed decisions based on aggregated and summarized data.
  • Performance Analysis: The functions assist in performance evaluation, comparison, and trend analysis.
  • Normalization and Standardization: These functions play a role in data normalization and standardization processes.
  • Subqueries and Joins: They can be used within subqueries and joins to filter, group, and aggregate data in more complex queries.

Overall, the SUM(), MIN(), MAX(), AVG(), and COUNT() functions provide fundamental tools for data manipulation, analysis, and reporting in SQL, enabling you to extract valuable insights from your database.

LIMIT Clause in MySQL

The LIMIT clause in MySQL is used to restrict the number of rows returned by a query. It allows you to specify the starting row and the maximum number of rows to be retrieved. The basic syntax of the LIMIT clause is as follows:

Syntax:

SELECT column1, column2, … FROM table_name LIMIT [offset,] row_count;

Here, offset specifies the number of rows to skip before starting to return the rows, and row_count specifies the maximum number of rows to be returned.

For example, if you want to retrieve the first 5 rows from a table called customers, you can use the following query: SELECT * FROM customers LIMIT n(n=1,2,3…);

Let’s consider ‘students’ table:

Let’s apply aggregate functions in MySQL:

Let’s apply other conditions and order by clause.

Let’s apply LIMIT clause with WHERE condition.

ORDER BY Clause

The ORDER BY clause in SQL is used to sort the result set of a query in a specified order. It arranges the rows returned by a query in either ascending (default) or descending order based on the values in one or more columns. Let’s see how applies order by clause in MySQL.

The real reason to use ORDER BY in SQL is to sort the results of a query in a specific order. This can be helpful for a variety of reasons, such as:

  • To make the results easier to read and understand.
  • To find specific records more easily.
  • To comply with business requirements.
  • To improve the performance of a query.

Syntax:

SELECT column1, column2,… FROM table WHERE condition ORDER BY COLUMN1,COLUMN2,.. ASC|DESC;

Note: In SQL all the records by-default order by ascending order.

Let’s understand by example:

Group by Clause

The GROUP BY clause in MySQL is used to group rows with the same values into summary rows. The clause returns one row for each group. In other words, it reduces the number of rows in the result set.

The GROUP BY clause is often used with aggregate functions such as SUM, AVG, MAX, MIN, and COUNT. For example, you could use the GROUP BY clause to find the total sales for each product category.

In real life, say I want to see from which city how much students are belonging, then we can use Group by clause.

The primary reason to use the GROUP BY clause in SQL is to aggregate and summarize data, allowing you to analyse trends, generate reports, and gain insights by grouping rows with the same values in specified columns and applying aggregate functions.

Syntax:

SELECT department, AVG(salary) AS avg_salary, COUNT(*) AS employee_count FROM employees GROUP BY department;

Let’s consider students table

Let’s apply group by clause into above table:

Let’s see another example:

download notes