6.0 KiB
Group by and built-in functions
GROUP BY clause
The GROUP BY clause groups a set of rows into a set of summary rows by values of columns or expressions. The GROUP BY clause returns one row for each group. In other words, it reduces the number of rows in the result set
Keyword: GROUP BY
Syntax: SELECT column1, column2, ... FROM table_name GROUP BY column1, column2, ...
In this syntax, you place the GROUP BY clause after the FROM and WHERE clauses. After the GROUP BY keywords, you place is a list of comma-separated columns or expressions to group rows.
MySQL evaluates the GROUP BY clause after the FROM and WHERE clauses and before the HAVING, SELECT, DISTINCT, ORDER BY and LIMIT clauses
Fields that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause at the end of the SQL statement.
For example, the following query will return the number of students in each batch:
SELECT
batch_id, AVG(iq)
FROM
students
GROUP BY batch_id;
You can also use the HAVING
clause to filter the result set.
For example, the following query will return the number of students in each batch where each batches average IQ is greater than or equal to 100:
SELECT
batch_id, AVG(iq)
FROM
students
GROUP BY batch_id
HAVING AVG(iq) >= 100;
Problems
MySQL functions
MySQL has a number of built-in functions that can be used to perform common tasks. They are divided in the following categories:
- Numeric
- String
- Date and time
- Miscellaneous
Numeric functions
Function | Description | Example |
---|---|---|
ABS | Absolute value | SELECT ABS(-1) |
CEIL | Round up to the nearest integer | SELECT CEIL(1.5) |
FLOOR | Round down to the nearest integer | SELECT FLOOR(1.5) |
ROUND | Round to the given precision | SELECT ROUND(1.54, 1) |
TRUNCATE | Truncate to the given precision | SELECT TRUNCATE(1.54, 1) |
RAND | Generate a random number | SELECT RAND() |
See more function here.
String functions
Function | Description | Example |
---|---|---|
LENGTH | Length of the string | SELECT LENGTH('Kattapa') |
LOWER | Convert to lowercase | SELECT LOWER('Kattapa') |
UPPER | Convert to uppercase | SELECT UPPER('Kattapa') |
LTRIM | Trim leading spaces | SELECT LTRIM(' Kattapa') |
RTRIM | Trim trailing spaces | SELECT RTRIM('Kattapa ') |
TRIM | Trim leading and trailing spaces | SELECT TRIM(' Kattapa ') |
SUBSTR | Extract a substring | SELECT SUBSTR('Namma Bengaluru', 1, 3) |
LEFT | Extract left substring | SELECT LEFT('Namma Bengaluru', 3) |
RIGHT | Extract right substring | SELECT RIGHT('Namma Bengaluru', 3) |
LOCATE | Find the position of a substring | SELECT LOCATE('Bengaluru', 'Namma Bengaluru') |
See more function here.
Date and time functions
Function | Description | Example |
---|---|---|
NOW | Current date and time | SELECT NOW() |
CURDATE | Current date | SELECT CURDATE() |
CURTIME | Current time | SELECT CURTIME() |
YEAR | Year of the date | SELECT YEAR('2020-01-01') |
MONTH | Month of the date | SELECT MONTH('2020-01-01') |
DAY | Day of the date | SELECT DAY('2020-01-01') |
DAYNAME | Day of the week | SELECT DAYNAME('2020-01-01') |
DAYOFWEEK | Day of the week | SELECT DAYOFWEEK('2020-01-01') |
DATE_ADD | Add a date | SELECT DATE_ADD('2020-01-01', INTERVAL 1 DAY) |
DATE_SUB | Subtract a date | SELECT DATE_SUB('2020-01-01', INTERVAL 1 DAY) |
DATEDIFF | Difference between two dates | SELECT DATEDIFF('2020-01-01', '2020-01-02') |
Miscellaneous functions
Function | Description | Example |
---|---|---|
IFNULL | Replace NULL values | SELECT IFNULL(batch_id, 'NO BATCH') |
COALESCE | Replace NULL values recursively | SELECT COALESCE(batch_id, phone, email, first_name) |
IF | Conditional expression | SELECT IF(batch_id = 1, 'YES', 'NO') |
CASE | Conditional expression | SELECT CASE WHEN batch_id = 1 THEN 'YES' ELSE 'NO' END |