SQL Functions – Single Row and Aggregate
Group By , Having clause
Joins in SQL
Functions in SQL
Function is used to perform some particular task and it returns zero or more values as a result. Functions can be applied to work on single or multiple records (rows) of a table.
Depending on their application in one or multiple rows, SQL functions are categorised as
- Single Row functions and
- Aggregate functions.
Single Row Functions
Single row functions are applied on a single value and return a single value. Also known as Scalar functions.
Types of Single Row Functions
(A) Math Functions
Math Functions accept numeric value as input and return a numeric value as a result.
Three commonly used numeric functions are POWER(), ROUND() and MOD().
POWER(X,Y) / POW(X,Y) : Calculates X to the power Y.
mysql> SELECT POWER(2,3);
Output:
8
ROUND(N,D)
Rounds off number N to D number of decimal places.
Note: If D=0, then it rounds off the number to the nearest integer.
mysql>SELECT ROUND(2912.564, 1);
Output:
2912.6
mysql> SELECT ROUND(283.2);
Output:
283
mysql> SELECT * FROM SALE WHERE Commission > 73000;
+—————+——–+———-+————–+———————-+———–+—————+——————-+
|invoiceno |carid | custid | saledate | paymentmode | empid | saleprice | Commission |
+—————+——–+———-+————–+———————-+———–+—————+——————-+
| I00001 |D001 | C0001 | 2019-01-24 | Credit Card | E004 | 613248.00 | 73589.64 |
| I00004 |D002 | C0001 | 2018-10-15 | Bank Finance| E007 | 659982.00 | 79198.84 |
| I00006 |S002 | C0002 | 2019-01-30 | Bank Finance | E007 | 620214.00 | 74425.68 |
+———-+——+——–+———–+————-+——-+———–+————+
3 rows in set (0.02 sec)
Example : Display InvoiceNo, SalePrice and Commission such that commission value is rounded off to 0.
mysql> SELECT InvoiceNo, SalePrice, Round(Commission,0) FROM SALE;
+—————-+—————+———————————+
| InvoiceNo | SalePrice | Round(Commission,0) |
+—————-+—————+———————————+
| I00001 | 613248.00 | 73590 |
| I00002 | 590321.00 | 70839 |
| I00003 | 604000.00 | 72480 |
| I00004 | 659982.00 | 79198 |
| I00005 | 369310.00 | 44317 |
| I00006 | 620214.00 | 74426 |
+———–+———–+———————+
6 rows in set (0.00 sec)
MOD(A, B) :
Returns the remain der after dividing number A by number B.
mysql> SELECT MOD(21, 2);
Output:
1
(B) String Functions
String functions can perform various operations on alphanumeric data which are stored in a table.
UCASE(string) OR UPPER(string) : Converts string into uppercase.
mysql> SELECT UCASE(“Informatics Practices”);
Output:
INFORMATICS PRACTICES
LOWER(string) OR LCASE(string) : Converts string into lowercase.
mysql> SELECT LOWER(“Informatics Practices”);
Output:
informatics practices
MID(string, pos, n) OR SUBSTRING(string, pos, n) OR SUBSTR(string, pos, n)
Returns a substring of size n starting from the specified position (pos) of the string. If n is not specified, it returns the substring from the position pos till end of the string.
mysql> SELECT MID(“Informatics”, 3, 4);
Output:
form
mysql> SELECT MID(‘Informatics’,7);
Output:
atics
LENGTH(string) : Return the number of characters in the specified string.
mysql> SELECT LENGTH(“Informatics”);
Output:
11
LEFT(string, N) : Returns N number of characters from the left side of the string.
mysql> SELECT LEFT(“Computer”, 4);
Output:
Comp
RIGHT(string, N) : Returns N number of characters from the right side of the string.
mysql> SELECT RIGHT(“SCIENCE”, 3);
Output:
NCE
INSTR(string, substring) : Returns the position of the first occurrence of the substring in the given string. Returns 0, if the substring is not present in the string.
mysql> SELECT INSTR(“Informatics”, “ma”);
Output:
6
LTRIM(string) : Returns the given string after removing leading white space characters.
mysql> SELECT LENGTH(“ DELHI”), LENGTH(LTRIM(“ DELHI”));
Output:
+—–+—–+
| 7 | 5 |
+—–+—–+
1 row in set (0.00 sec)
RTRIM(string) : Returns the given string after removing trailing white space characters.
mysql>SELECT LENGTH(“PEN “)LENGTH(RTRIM(“PEN “));
Output:
+—+—-+
| 5 | 3 |
+—+—-+
1 row in set (0.00 sec)
TRIM(string) : Returns the given string after removing both leading and trailing white space characters.
mysql> SELECT LENGTH(“MADAM “),LENGTH(TRIM(“MADAM “));
Output:
+—+—+
| 9 | 5 |
+—+—+
1 row in set (0.00 sec)
(C) Date and Time Functions
There are various functions that are used to perform operations on date and time data.
NOW()
It returns the current system date and time.
mysql> SELECT NOW();
Output:
2019-07-11 19:41:17
DATE() It returns the date part from the given date/time expression.
mysql> SELECT DATE(NOW());
Output:
2019-07-11
MONTH(date) : It returns the month in numeric form from the date.
mysql> SELECT MONTH(NOW());
Output:
7
MONTHNAME(date) : It returns the month name from the specified date.
mysql> SELECT MONTHNAME(“2003-11-28”);
Output:
November
YEAR(date) : It returns the year from the date. mysql> SELECT YEAR(“2003-10-03”);
Output:
2003
DAY(date) : It returns the day part from the date.
mysql> SELECT DAY(“2003-03-24”);
Output:
24
DAYNAME(date) : It returns the name of the day from the date.
mysql> SELECT DAYNAME(“2019-07-11”);
Output:
Thursday
Aggregate Functions
Aggregate functions are also called Multiple Row functions. These functions work on a set of records as a whole and return a single value for each column of the records on which the function is applied.
Differences between Single and Multiple Row Functions
Single Row Function | Multiple row function |
1. It operates on a single row at a time. 2. It returns one result per row. 3. It can be used in Select, Where, and Order by clause. 4. Math, String and Date functions are examples of single row functions. | 1. It operates on groups of rows. 2. It returns one result for a group of rows. 3. It can be used in the select clause only. 4. Max(), Min(), Avg(), Sum(), Count() and Count(*) are examples of multiple row functions. |
Aggregate Functions in SQL
MAX(column) : Returns the largest value from the specified column.
mysql> SELECT MAX(Price) FROM INVENTORY;
Output:
673112.00
MIN(column) : Returns the smallest value from the specified column.
mysql> SELECT MIN(Price) FROM INVENTORY;
Output:
355205.00
AVG(column) : Returns the average of the values in the specified column.
mysql> SELECT AVG(Price) FROM INVENTORY;
Output:
576091.625000
SUM(column) : Returns the sum of the values for the specified column.
mysql> SELECT SUM(Price) FROM INVENTORY;
Output:
4608733.00
COUNT() : Returns the number of records in a table. Note: In order to display the number of records that matches a particular criteria in the table, we have to use COUNT() with WHERE clause.
mysql> SELECT COUNT() from MANAGER;
|count() |
+—+
| 4 |
+—+
COUNT(column) : Returns the number of values in the specified column ignoring the NULL values.
Note:
In this example, let us consider a MANAGER table having two attributes and four records.
mysql> SELECT * from MANAGER;
+——– +——————+
| MNO | MEMNAME |
+——– +——————+
| 1 | AMIT |
| 2 | KAVREET |
| 3 | KAVITA |
| 4 | NULL |
+———+—————–+
4 rows in set (0.00 sec)
mysql> SELECT COUNT(MEMNAME) FROM MANAGER;
+——————————+
| COUNT(MEMNAME) |
+——————————-+
| 3 |
+——————————-+
1 row in set (0.01 sec)
My SQL – Structured Query Language
Grouping Records
GROUP BY Clause in SQL
Group by clause, groups the rows together that contains the same values in a specified column.
HAVING Clause in SQL is used to specify conditions on the rows with Group By clause.
We can use the aggregate functions (COUNT, MAX, MIN, AVG and SUM) to work on the grouped values.
Sales Table
a) Display the number of Cars purchased by each Customer from SALE table.
mysql> SELECT CustID, COUNT() “Number of Cars” FROM SALE GROUP BY CustID;
CustID | Number of Cars |
C0001 | 2 |
C0002 | 2 |
C0003 | 1 |
C0004 | 1 |
4 rows in set (0.00 sec)
b) Display the Customer Id and number of cars purchased if the customer purchased more than 1 car from SALE table.
mysql> SELECT CustID, COUNT() FROM SALE GROUP BY
CustID HAVING Count()>1;
c) Display the number of people in each category of payment mode from the table SALE.
mysql> SELECT PaymentMode, COUNT(PaymentMode)
FROM SALE GROUP BY Paymentmode ORDER BY Paymentmode;
PaymentMode | Count(PaymentMode) |
Bank Finance | 2 |
Cheque | 1 |
Credit Card | 2 |
Online | 1 |
4 rows in set (0.00 sec)
d) Display the PaymentMode and number of payments made using that mode more than once.
mysql> SELECT PaymentMode, Count(PaymentMode)
FROM SALE GROUP BY Paymentmode HAVING COUNT(*)>1
ORDER BY Paymentmode;
PaymentMode | Count(PaymentMode) |
Bank Finance | 2 |
Credit Card | 2 |
2 rows in set (0.00 sec)