🗽
Abhishek’s Notes/
SQL
SQL
SQL
SQL

SQL

 
Group By Syntax
SELECT column1, function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition
ORDER BY column1, column2;


function_name: Name of the function used for example, SUM() , AVG().
Example
SELECT product_name, product_cost  
FROM products  
GROUP BY product_name, product_cost  
HAVING SUM(product_cost) > 3.5  
ORDER BY product_cost;
 
 
 

1. Aggregation Functions (group calculations)

Function
What it does
Example
COUNT(*)
Count number of rows
SELECT COUNT(*) FROM table;
SUM(column)
Sum of a column
SELECT SUM(price) FROM sales;
AVG(column)
Average value
SELECT AVG(age) FROM users;
MIN(column)
Minimum value
SELECT MIN(score) FROM results;
MAX(column)
Maximum value
SELECT MAX(score) FROM results;
GROUP_CONCAT(column)
Join group values into a string
SELECT GROUP_CONCAT(name) FROM users GROUP BY city;

2. Conditional Functions

Function
What it does
Example
IF(condition, true_value, false_value)
Simple IF logic
SELECT IF(age>18, 'Adult', 'Minor') FROM users;
CASE WHEN
More complex conditions
SELECT CASE WHEN score > 90 THEN 'A' ELSE 'B' END FROM results;
IFNULL(column, default)
Replace NULL with a default
SELECT IFNULL(name, 'Unknown') FROM users;
COALESCE(col1, col2, ..., default)
First non-null value
SELECT COALESCE(nickname, name, 'Guest') FROM users;

3. String Functions

Function
What it does
Example
LENGTH(string)
Length in bytes
SELECT LENGTH('Hello');
CHAR_LENGTH(string)
Length in characters
SELECT CHAR_LENGTH('नमस्ते');
CONCAT(str1, str2, ...)
Merge strings
SELECT CONCAT(first_name, ' ', last_name) FROM users;
UPPER(string)
Convert to uppercase
SELECT UPPER(name) FROM users;
LOWER(string)
Convert to lowercase
SELECT LOWER(name) FROM users;
SUBSTRING(string, start, length)
Extract part of string
SELECT SUBSTRING(name, 1, 3) FROM users;
REPLACE(string, from_str, to_str)
Replace substring
SELECT REPLACE(name, 'a', '@') FROM users;

4. Math Functions

Function
What it does
Example
ABS(number)
Absolute value
SELECT ABS(-10);
ROUND(number, decimals)
Round to decimals
SELECT ROUND(3.14159, 2);
FLOOR(number)
Largest integer ≤ number
SELECT FLOOR(2.9);
CEIL(number)
Smallest integer ≥ number
SELECT CEIL(2.1);
MOD(x, y)
Remainder (modulo)
SELECT MOD(10, 3);
RAND()
Random number [0,1)
SELECT RAND();

5. Date and Time Functions

Function
What it does
Example
NOW()
Current date and time
SELECT NOW();
CURDATE()
Current date
SELECT CURDATE();
CURTIME()
Current time
SELECT CURTIME();
DATE_ADD(date, INTERVAL x unit)
Add time
SELECT DATE_ADD(NOW(), INTERVAL 5 DAY);
DATE_SUB(date, INTERVAL x unit)
Subtract time
SELECT DATE_SUB(NOW(), INTERVAL 2 MONTH);
DATEDIFF(date1, date2)
Difference in days
SELECT DATEDIFF('2025-12-31', '2025-01-01');
YEAR(date)
Extract year
SELECT YEAR(birthday) FROM users;
MONTH(date)
Extract month
SELECT MONTH(order_date) FROM sales;

6. Others (Utility / Helpful)

Function
What it does
Example
ISNULL(column)
1 if NULL, 0 otherwise
SELECT ISNULL(name) FROM users;
NULLIF(a, b)
NULL if a = b, else a
SELECT NULLIF(price, 0) FROM products;
CAST(expr AS TYPE)
Change data type
SELECT CAST(price AS CHAR) FROM products;
FORMAT(number, decimals)
Format number with commas
SELECT FORMAT(12345.6789, 2);
 
Built with Potion.so