![]() If one of the rows has a NULL in this column, SUM() omits this row from the calculation. the alias) is sum_quantity.Īs you see, the sum of values in the quantity column in the table productis 7. ![]() The SUM() function adds all values from the quantity column and returns the total as the result of the function. In this query, we use SUM() alone in the SELECT statement. If you want to sum values stored in one column, use SUM() with that column’s name as the argument. The product table stores data we’ll use to calculate the sum of given values. I will explain this issue more in the next section.įirst, let’s get to know the data on which we will build our SQL SUM() queries. The DISTINCT keyword is optional when it’s used, SUM() only adds non-repeating values. The values provided in the argument are summed up and returned by the SUM() function. So, total order amount for all the orders that are in OPEN state is 162.67.As you see, this function requires only one argument: a column name or an expression. mysql> SELECT SUM(amount) AS open_order_total_amount FROM orders WHERE orderstatus = 'OPEN' In the following example we will display the sum i.e., total of the order amount for OPEN orders in the orders table. mysql> SELECT SUM(amount) AS total_amount FROM orders When using an alias the output will look like the following. So, total order amount in the orders table is 272.87. In the following example we will display the sum i.e., total of the order amount in the orders table. So, average order amount placed by employeeid 'e04' is 54.745000. mysql> SELECT AVG(amount) AS avg_amount FROM orders WHERE employeeid = 'e04' In the following example we will display the average order amount for the employeeid 'e04' in the orders table. mysql> SELECT AVG(amount) AS avg_amount FROM orders We can also use an alias in the above query. So, the average order amount in the orders table is 45.478333. In the following example we will display the average order amount in the orders table. We use the AVG function to find the average. So, employeeid 'e01' has placed max value order of 25.50. mysql> SELECT MAX(amount) AS max_amount FROM orders WHERE employeeid = 'e01' In the following example we will display the max order amount for the employeeid 'e01' in the orders table. mysql> SELECT MAX(amount) AS max_amount FROM orders ![]() We can use an alias for the given query like the following. So, maximum or highest order amount in the orders table is 100.70. In the following example we will display the maximum order amount in the orders table. We use the MAX function to find the maximum value. So, employeeid 'e03' has a minimum order amount of 15.00. mysql> SELECT MIN(amount) AS min_amount FROM orders WHERE employeeid = 'e03' In the following example we will display the minimum order amount for the employeeid 'e03' in the orders table. mysql> SELECT MIN(amount) AS min_amount FROM orders So, minimum order amount is 9.50 in the orders table. In the following example we will display the minimum order amount in the orders table. We use the MIN function to find the minimum value. So, total 5 unique employeeid is present in the orders table. mysql> SELECT COUNT(DISTINCT employeeid) AS total_unique_employeeids FROM orders In the following example we are counting total number of distinct employees who placed an order. Syntax: SELECT COUNT(DISTINCT column_name) If we want to count distinct value in a column then we use DISTINCT in the COUNT function. So, employeeid 'e04' placed total 2 orders. mysql> SELECT COUNT(*) AS total_order FROM orders WHERE employeeid = 'e04' In the following example we are counting total number of orders placed by employeeid 'e04'. mysql> SELECT COUNT(*) AS total_order FROM orders We can even use an alias in the above query. So, there are total 6 orders in the orders table. In the following example we are listing total number of orders in the orders table. ![]() We use the COUNT function to find the number of rows matching the given condition. | orderid | employeeid | amount | orderstatus | lastmodified | created | List of aggregate functions that we will cover in this tutorial.įor this tutorial we will be using the orders table shown below. These are the functions in MySQL that performs some calculation on a set of values and then returns a single value.Įxample: The SUM(column_name) function computes the sum of the values present in a given column. In this tutorial we will learn about some of the most commonly used aggregate functions like COUNT, MIN, MAX, AVG and SUM in MySQL.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |