Database design

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();