Section 1
Lesson 304.5 - Aggregate Functions and Operators
Learning Objective:
By the end of this lesson, learners will be able to:
Describe aggregate functions.
Demonstrate aggregate functions.
Section 1
SQL Aggregate Function
Table of Contents
Overview of Aggregate Function.
String Functions.
Numeric/Math Functions.
Formats for Date and Time.
Advanced Functions.
MD5() Function.
CAST() Function.
Overview of Aggregate Function
SQL has many built-in functions, which are broken into many categories. Four common categories are listed below:
String Functions.
Numeric/Math Functions.
Date and Time Functions.
Advanced Functions.
Example: MAX(), Min(), Count(), ASCII(), CHAR_LENGTH(), CHARACTER_LENGTH (), CONCAT(), FORMAT(), INSERT(), LCASE(), LENGTH(), LOCATE(), LOWER(), LPAD(), LTRIM(), MID(), POSITION(), REPEAT, REPLACE(), REVERSE(), RIGHT(), RPAD(), SUBSTR(), TRIM(), UPPER(), SUM() Function and more.
Reference to below link for all functions:
Note: We cannot use aggregate functions within the WHERE clause.
String Functions
Let's demonstrate the most commonly used SQL String functions that allow you to manipulate character string data effectively.
String Functions → REPLACE() Function
SQL provides you with a useful string function called REPLACE(), that allows you to replace a string in a column of a table by a new string or replaces all occurrences of a substring within a string, with a new substring.
Syntax of the REPLACE() function is: REPLACE(str,old_string,new_string);
The REPLACE() function has three parameters. It replaces the old_string by the new_string in the string.
The REPLACE() function is very handy to search and replace text in a table such as updating obsolete URL or correcting a spelling mistake, etc.
Note: The REPLACE() function performs a case-sensitive replacement.
Example:
SELECT REPLACE('abc.org', 'abc', 'perscholas'); #output → perscholas.org
SELECT REPLACE('abc abc', 'a', 'B'); #output → 'Bbc Bbc'
String Functions → TRIM() Function
The TRIM() function removes all specified characters, from either the beginning or the end of a string.
TRIM() to help you clean up the data. The following illustrates the syntax of the TRIM() function.
TRIM( [ {BOTH|LEADING|TRAILING} [removed_character] ] FROM Given_String);
The TRIM() function provides a number of options. You can use the LEADING, TRAILING, or BOTH options to explicitly instruct the TRIM() function to remove leading, trailing, or both unwanted characters from a string.
Example: Note: If you do not specify a value for the first parameter (LEADING, TRAILING, BOTH), the TRIM() function will default to BOTH and remove character or string from both the front and end of the string.
SELECT TRIM(LEADING '@' FROM '@perscholas.org@@@'); # Result → perscholas.org@@@
SELECT TRIM(TRAILING '@' FROM '@perscholas.org@@@'); # Result → @perscholas.org
SELECT TRIM(BOTH '@' FROM '@perscholas.org@@@');
SELECT TRIM( '@' FROM '@perscholas.org@@@');
SELECT TRIM(LEADING '0' FROM '000123');
SELECT TRIM(TRAILING '1' FROM 'Tech1');
SELECT TRIM(BOTH '123' FROM '123Tech123');
# Result → perscholas.org
# Result → perscholas.org
# Result → '123'
# Result → 'Tech'
String Functions 🡪 COUNT(*) and COUNT(fieldname)
The COUNT(*) function returns the total number of records meeting the (optional) WHERE criteria:
Example:
SELECT COUNT(*) FROM products; #Result: 110 (result might be vary)
SELECT COUNT(productLine) FROM products; # Result: 110
We get the same result in both above queries.
The COUNT(*) function is often used with a GROUP BY clause to return the number of elements in each group. For example, that statement below uses the COUNT() function with the GROUP BY clause to return the number of products in each product line:
SELECT productLine, COUNT(*) FROM products GROUP BY productLine;
Note: COUNT(*) is unique among the aggregate functions – it counts NULL values. This is because it counts rows, not fields.
String Functions 🡪 MAX() and MIN()
MIN() and MAX() functions return single values from a recordset. Note that selecting MIN() or MAX() from an empty resultset returns a NULL.
SELECT MAX(amount), MIN(amount) FROM payments;
#Result: MAX(amount) = 120166.58 MIN(amount) = 615.45
The below query uses the MAX() function to find the largest payment in 2004:
SELECT MAX(amount) as largest_payment_2004 FROM payments WHERE YEAR(paymentDate) = 2004; #Result → largest_payment_2004 = 116208.40
The below query uses the MIN() function to find the lowest buy price of all motorcycles from the productline table.
SELECT MIN(buyPrice) FROM products WHERE productline = 'Motorcycles'; #Result → MIN(buyPrice) = 24.14
String Functions 🡪 CONCAT() Function
To join two or more strings into one, we can use the CONCAT() function, which takes one or more string arguments and concatenates them into a single string. The CONCAT() function requires a minimum of one parameter; otherwise, it raises an error.
Example:
CONCAT(string1,string2, ... );
SELECT CONCAT('Per Scholas','-','NON-Profit');
Syntax:
Example: CONCAT() Function
Consider the customers table in the classicmodels database.
To get the full names of contacts, we can use the CONCAT() function to concatenate first name, space, and last name, as shown in the below query.
SELECT concat(contactFirstName,' ',contactLastName) AS Fullname FROM customers;
String Functions 🡪 UPPER() Function
The UPPER() function converts all characters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.
Example:
SELECT UPPER('per scholas');
Example: UPPER() Function Consider the customer's table in the classicmodels database. To get the full names of contacts, we can use the CONCAT() function to concatenate first name, space, and last name. We can convert all characters into the upper letters by using the UPPER() function as shown in the below query.
SELECT concat(contactFirstName,' ',contactLastName), UPPER(concat(contactFirstName,' ',contactLastName)) AS Fullname FROM customers;
Numeric/Math Functions
Let's demonstrate the most commonly used SQL Numeric/Math Functions. MySQL Math Functions are the MySQL built-in functions, which refer the numeric type functions and commands to operate the mathematical logics.
Numeric/Math Functions → SUM() Function
The SUM() function calculates the sum of a set of values. Note: NULL values will be ignore in SUM().
Example 1
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
# Above query return the sum of the "Quantity" field in the "OrderDetails" table
Example 2
SELECT SUM(amount) AS "Total amount" FROM payments; #Result → 8853839.23
Example 3
SELECT SUM(quantityOrdered * priceEach) as orderTotal FROM orderdetails WHERE orderNumber = 10100;
#Result → orderTotal = 10223.83
Numeric/Math Functions → AVG() Function
AVG(): Return the average of non-NULL values.
The below query uses the AVG() function to calculate the average buy price of all products from the products table:
SELECT AVG(buyprice) as 'Average Price' FROM products; #Result → 54.395182
The following example uses the AVG() function to calculate the average buy price of products in the product line Classic Cars:
SELECT AVG(buyprice) as 'Average Classic Cars Price' FROM products WHERE productline = 'Classic Cars';
#Result → Average Classic Cars Price = 64.446316
Note: SUM() and AVG() functions return a value calculated across all rows of a recordset.
Numeric/Math Functions → MOD() Function
The MOD() Function returns the remainder of one number divided by another.
Syntax: MOD(dividend,divisor).
The MOD() function accepts two arguments:
dividend is a literal number or a numeric expression to divide.
divisor is a literal number or a numeric expression by which to divide the dividend.
The MOD() function returns the remainder of dividend divided by divisor. If the divisor is zero, the MOD(dividend, 0) returns NULL.
Examples:
SELECT MOD(11, 3); #Result: 2
#This above query divides the number 11 by 3. It returns 2 as the integer portion of the result.
SELECT MOD(12, 5); #Result: 2
SELECT MOD(12, 0.18); #Result: 0.12
SELECT MOD( o.quantityOrdered , 2) AS `MOD_DATA`, o.quantityOrdered FROM orderdetails o;
Numeric/Math Functions → ROUND() Function
The ROUND() function allows you to round a number to a specified number of decimal places.
Syntax: ROUND(n,[d]) - In this syntax, n is a number to be rounded, and d is the number of decimal places to which the number is rounded. The number of decimal places (d) is optional, it defaults to zero if skipped.
The number of decimal places (d) can be positive or negative. If it is negative, the d digits left of the decimal point of the number n become 0.
Examples:
SELECT ROUND(135.375, 2);
SELECT ROUND(-125.315);
SELECT ROUND(121.55,-2);
SELECT ROUND(121.55,-1);
SELECT ROUND(125.315, 1);
SELECT ROUND(125.315, -2); # Result: 100
SELECT ROUND(priceEach, 0), priceEach FROM orderdetails;
# Result: 135.38 //Round the number to 2 decimal places:
See also the FLOOR, CEIL, CEILING, and TRUNCATE functions.
Numeric/Math Functions → TRUNCATE() Function
The TRUNCATE() Function truncates a number to a specified number of decimal places:
Syntax: TRUNCATE(X,D) - In this syntax:
X is a literal number or a numeric expression to be truncated.
D is the number of decimal places to truncate to. If D is negative, then the TRUNCATE() function causes D digits left of the decimal point of X to become 0. In case D is 0, then the return value has no decimal point.
Notice that the TRUNCATE() function is similar to the ROUND() function in terms of reducing the number of decimal places. However, the TRUNCATE() function does not perform any rounding as the ROUND() function does.
Examples:
SELECT TRUNCATE(125.315, 0); # Result: 125
SELECT TRUNCATE(125.315, 1); # Result: 125.3
SELECT TRUNCATE(125.315, 2); # Result: 125.31
SELECT TRUNCATE(125.315, -1); # Result: 120
SELECT TRUNCATE(125.315, -2); # Result: 100
SELECT TRUNCATE(-125.315, 0); # Result: -125
Examples:
Let's demonstrate the most commonly used SQL Date and Time Functions. The SQL date and time functions are responsible for extracting the data section from the specified date or expression “DateTime.”
Formats for Date and Time
Let’s view some of the Data Types formats used in SQL for DATE and TIME functions and for storing the values:
DATE: YYYY-MM-DD
DATETIME: YYYY-MM-DD HH:MM:SS
TIMESTAMP: YYYY-MM-DD HH:MM:SS
YEAR: YYYY or YY
Date Functions → CURRENT_DATE() Function
The CURRENT_DATE() function returns the current date, as a 'YYYY-MM-DD' format. If used in a string context.
Syntax for the CURRENT_DATE function in SQL is:
CURRENT_DATE() or CURDATE()
Example:
SELECT CURRENT_DATE() ; # Result → September 1, 2022 (Note: Your result will be different.)
SELECT CURDATE(); # Result → September 1, 2022 (Note: Your result will be different.)
INSERT INTO orders (orderNumber, orderDate, requiredDate, shippedDate,status,customerNumber) VALUES (20100, CURRENT_DATE(),CURRENT_DATE(),CURRENT_DATE(),'In-Progress',363 );
Date Functions → CURRENT_TIME() Function
The CURRENT_TIME() function returns the current time.
The CURRENT_TIME() function will return the current date as :
'HH:MM:SS' format, if used in a string context.
HHMMSS format, if used in a numeric context.
Examples:
SELECT CURRENT_TIME(); # Result: '10:42:14'
SELECT CURRENT_TIME() + 0; # Result: 104214
SELECT CURRENT_TIME() + 1; # Result: 104215
SELECT CURRENT_TIME(3); # Result: 15:08:31.500
Date Functions → CURRENT_TIMESTAMP() Function
The CURRENT_TIMESTAMP() function returns the current date and time.
The CURRENT_TIMESTAMP() function will return the current date as:
'YYYY-MM-DD HH:MM:SS' format, if used in a string context.
YYYYMMDDHHMMSS format, if used in a numeric context.
Examples:
SELECT CURRENT_TIMESTAMP(); # Result: 15:21:01
Date Functions →MONTH(‘date’) Function
The MONTH() function returns the month portion for a given date (a number from 1 to 12).
Syntax: MONTH(‘date’)
Example:
SELECT MONTH('January 28, 2014'); #Result → 1
SELECT MONTH("June 15, 2017 09:34:21"); #Result → 6
SELECT MONTH(CURDATE()); #Result → This would display the month portion of the current system date of your system/computer.
Date Functions → DATE_FORMAT() Function
To format a date() value to a specific format, you can use the DATE_FORMAT() function.
Syntax: DATE_FORMAT(date,format), function accepts two arguments:
date: is a valid date value that you want to format.
format: is a format string that consists of predefined specifiers.
Each specifier is preceded by a percentage sign( % ). Visit below link for a list of predefined specifiers. https://www.techonthenet.com/mysql/functions/date_format.php
Examples:
SELECT DATE_FORMAT('April 28, 2021', '%Y'); #Result → 2021
SELECT DATE_FORMAT(current_date(), '%Y'); #Result → 2022 // this would display the year portion of the current system date of your system/computer
SELECT DATE_FORMAT('February 1, 2014', '%M %e %Y'); #Result → 'February 1 2014'
SELECT DATE_FORMAT('February 28, 2014', '%W, %M %e, %Y'); #Result → 'Friday, February 28, 2014'
SELECT DATE_FORMAT('February 28, 2014', '%W'); #Result: 'Friday'
Example: DATE_FORMAT() Function
SELECT p.paymentDate AS Actual Date, DATE_FORMAT(p.paymentDate, '%W %e %M %Y') AS Formatted Date FROM payments p;
Date Functions → DATEDIFF() Function
SELECT DATEDIFF('January 28, 2021', 'January 27, 2021'); #Result: 1
SELECT DATEDIFF('January 28, 2021 11:41:14', 'January 27, 2021 12:10:08'); #Result: 1
SELECT DATEDIFF('February 15, 2029', 'February 10, 2021'); #Result: 2927
SELECT DATEDIFF('January 28, 2014', 'December 31, 2013'); #Result: 28
SELECT DATEDIFF('December 31, 2013', 'January 28, 2014'); #Result: -28
SELECT DATEDIFF(CURDATE(), 'February 14, 2014'); # Result: This would display the difference between current system date and 'February 14, 2014'
The DATEDIFF() function calculates the number of days between two DATE, DATETIME, or TIMESTAMP values.
Syntax: DATEDIFF(date_expression_1, date_expression_2);
Advanced Functions → IF() Function
The IF() function returns one value if a condition evaluates to TRUE, or another value if it evaluates to FALSE.
Syntax: IF( condition, [value_if_true], [value_if_false] )
Examples:
SELECT IF(100<200, 'yes', 'no'); #Result → 'yes'
SELECT IF(500<1000, 5, 10); #Result → 5
SELECT OrderNumber, quantityOrdered, IF(quantityOrdered>30, "MORE", "LESS") FROM OrderDetails;
Example: IF() Function
Example 1: Let's utilize the OrderDetails table from the classicmodel database. The following query will return "MORE" if the number of items ordered is greater than 30, and "LESS" if the number of items ordered is less than 30.
IF(quantityOrdered>30, "MORE", "LESS") SELECT OrderNumber, quantityOrdered, FROM classicmodels.OrderDetails;
Example 2: The IF() function is useful when it combines with an aggregate function. Suppose you want to know how many orders have been shipped and cancelled. You can use the IF() function with the SUM() function as the following query:
SELECT SUM(IF(status = 'Shipped', 1, 0)) AS Shipped, SUM(IF(status = 'Cancelled', 1, 0)) AS Cancelled FROM classicmodels.orders;
Advanced Functions → IFNULL() unction
The IFNULL() Function is one of the MySQL control flow functions that accepts two arguments and returns the first argument if it is not NULL. Otherwise, the IFNULL function returns the second argument.
Consider the customer's table. You will notice that column addressLine2 has some NULL values. We can set the condition if addressLine2 is NULL, and then print addressLine1.
The query is shown on the next slide.
SELECT customerNumber, addressLine1, addressLine2, IFNULL(addressLine2, addressLine1) as CustomerAddress from customers;
MD5() Function
The MD5() function Calculates an MD5 128-bit checksum for a string. The value is returned as a hash key (binary string of 32 hex digits), or NULL if the argument was NULL.
Syntax: MD5(str);
SELECT MD5('MypasswordFor20Dollar$');
CAST() Function
The CAST() function converts a value from one datatype to another datatype.
Syntax: CAST(value AS type).
value: The value to convert to another datatype.
type: The data type that you wish to convert value to. It can be one of the following: DATE, DATETIME, TIME, CHAR, SIGNED, UNSIGNED, BINARY
Examples:
SELECT CAST('February 28, 2014' AS DATE); #Result: 'February 28, 2014'
SELECT CAST(125 AS CHAR); #Result: '125'
SELECT CAST(4-6 AS UNSIGNED); #Result: 18446744073709551614
UNSIGNED converts a value to the UNSIGNED data type that contains the unsigned
64-bit integer.
SELECT CAST("14:06:10" AS TIME); #Result: 14:06:10
Visit below link for more SQL functions https://dev.mysql.com/doc/refman/8.0/en/functions.html
Hands-On Lab: Aggregate Function
Complete the lab GLAB - 304.5.1 - Aggregate Functions. You can find this Lab on Canvas under Assignment section. Note: If you have any technical questions while performing the lab activity, ask your instructors for assistance.
Last updated