Perform calculations across multiple rows and return a single value.
Key Functions
Function
Description
Example
SUM()
Total of values
SELECT SUM(salary) FROM emp;
AVG()
Average value
SELECT AVG(salary) FROM emp;
COUNT()
Number of non-NULL values
SELECT COUNT(name) FROM emp;
MAX()
Highest value
SELECT MAX(salary) FROM emp;
MIN()
Lowest value
SELECT MIN(salary) FROM emp;
COUNT(*)
Counts all rows (including NULLs)
SELECT COUNT(*) FROM emp;
Practical Examples
-- Total salary expenditure
SELECT SUM(salary) FROM emp; -- Returns 161000-- Average salary in Sales department
SELECT AVG(salary) FROM emp WHERE dept='Sales'; -- Returns 29500-- Number of employees in HR
SELECT COUNT(*) FROM emp WHERE dept='HR'; -- Returns 2 (includes NULL)-- Highest salary in IT department
SELECT MAX(salary) FROM emp WHERE dept='IT'; -- Returns 30000
COUNT(*) vs COUNT(column)
COUNT(*) counts all rows
COUNT(column) counts non-NULL values only
MySQL Functions
A. String Functions
Function
Description
Example
CONCAT()
Joins strings
SELECT CONCAT(name, ' - ', dept);
SUBSTRING()
Extracts text
SELECT SUBSTRING('Database',3,4); → ‘taba’
UPPER()/LOWER()
Case conversion
SELECT UPPER(name) FROM emp;
TRIM()
Removes spaces
SELECT TRIM(' Hello '); → ‘Hello’
LENGTH()
String length
SELECT LENGTH('MySQL'); → 5
INSTR()
Finds substring
SELECT INSTR('Programming','gram'); → 5
B. Numeric Functions
Function
Description
Example
ROUND()
Rounds numbers
SELECT ROUND(15.789, 2); → 15.79
MOD()
Remainder
SELECT MOD(11, 3); → 2
POWER()
Exponentiation
SELECT POWER(2, 4); → 16
SQRT()
Square root
SELECT SQRT(25); → 5
TRUNCATE()
Cuts decimals
SELECT TRUNCATE(15.789, 1); → 15.7
C. Date/Time Functions
Function
Description
Example
CURDATE()
Current date
SELECT CURDATE(); → 2023-11-20
NOW()
Current datetime
SELECT NOW(); → 2023-11-20 14:30:00
MONTH()
Extracts month
SELECT MONTH('2023-08-15'); → 8
DAYNAME()
Weekday name
SELECT DAYNAME('2023-12-25'); → ‘Monday’
DATEDIFF()
Days between dates
SELECT DATEDIFF('2023-12-31','2023-01-01'); → 364
NOW() vs SYSDATE()
NOW() returns execution start time
SYSDATE() returns current time when called
SELECT NOW(), SLEEP(2), NOW(); -- Same timestamp
SELECT SYSDATE(), SLEEP(2), SYSDATE(); -- Different timestamps
