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:

  1. SELECT CURRENT_DATE() ; # Result → September 1, 2022 (Note: Your result will be different.)

  2. SELECT CURDATE(); # Result → September 1, 2022 (Note: Your result will be different.)

  3. 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

  • SELECT CURRENT_TIMESTAMP(), CURRENT_TIME();

    Result: September 2, 2022 15:21:01 | 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.

PreviousAggregate Functions and OperatorsNextSection 2

Last updated