Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Perform a bitwise XOR operation. This function returns the result of performing a bitwise XOR on all values in a given expression.
BIT_XOR(expr) [over_clause]Returns the bitwise XOR of all bits in expr. The calculation is performed with 64-bit () precision. It is an , and so can be used with the clause.
If no rows match, BIT_XOR will return a value with all bits set to 0. NULL values have no effect on the result unless all results are NULL, which is treated as no match.
BIT_XOR can be used as a with the addition of the over_clause.
As an :
No match:
This page is licensed: GPLv2, originally from
Perform a bitwise OR operation. This function returns the result of performing a bitwise OR on all values in a given expression.
BIT_OR(expr) [over_clause]Returns the bitwise OR of all bits in expr. The calculation is performed with 64-bit () precision. It is an , and so can be used with the clause.
If no rows match, BIT_OR will return a value with all bits set to 0. NULL values have no effect on the result unless all results are NULL, which is treated as no match.
BIT_OR can be used as a with the addition of the over_clause.
As an :
No match:
This page is licensed: GPLv2, originally from
Calculate the average value. This function computes the arithmetic mean of a numeric expression, ignoring NULL values.
AVG() returns NULL if there were no matching rows.
AVG() can be used as a window function.
Commonly, AVG() is used with a GROUP BY clause:
Be careful to avoid this common mistake, not grouping correctly and returning mismatched data:
As a window function:
This page is licensed: GPLv2, originally from fill_help_tables.sql
If no rows match, BIT_AND will return a value with all bits set to 1. NULL values have no effect on the result unless all results are NULL, which is treated as no match.
BIT_AND can be used as a window function with the addition of the over_clause.
As an aggregate function:
No match:
This page is licensed: GPLv2, originally from fill_help_tables.sql
AVG([DISTINCT] expr)CREATE TABLE sales (sales_value INT);
INSERT INTO sales VALUES(10),(20),(20),(40);
SELECT AVG(sales_value) FROM sales;
+------------------+
| AVG(sales_value) |
+------------------+
| 22.5000 |
+------------------+
SELECT AVG(DISTINCT(sales_value)) FROM sales;
+----------------------------+
| AVG(DISTINCT(sales_value)) |
+----------------------------+
| 23.3333 |
+----------------------------+CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
SELECT name, AVG(score) FROM student GROUP BY name;
+---------+------------+
| name | AVG(score) |
+---------+------------+
| Chun | 74.0000 |
| Esben | 37.0000 |
| Kaolin | 72.0000 |
| Tatiana | 85.0000 |
+---------+------------+SELECT name,test,AVG(score) FROM student;
+------+------+------------+
| name | test | MIN(score) |
+------+------+------------+
| Chun | SQL | 31 |
+------+------+------------+CREATE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
SELECT name, test, score, AVG(score) OVER (PARTITION BY test)
AS average_by_test FROM student_test;
+---------+--------+-------+-----------------+
| name | test | score | average_by_test |
+---------+--------+-------+-----------------+
| Chun | SQL | 75 | 65.2500 |
| Chun | Tuning | 73 | 68.7500 |
| Esben | SQL | 43 | 65.2500 |
| Esben | Tuning | 31 | 68.7500 |
| Kaolin | SQL | 56 | 65.2500 |
| Kaolin | Tuning | 88 | 68.7500 |
| Tatiana | SQL | 87 | 65.2500 |
| Tatiana | Tuning | 83 | 68.7500 |
+---------+--------+-------+-----------------+BIT_AND(expr) [over_clause]CREATE TABLE vals (x INT);
INSERT INTO vals VALUES(111),(110),(100);
SELECT BIT_AND(x), BIT_OR(x), BIT_XOR(x) FROM vals;
+------------+-----------+------------+
| BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+------------+-----------+------------+
| 100 | 111 | 101 |
+------------+-----------+------------+CREATE TABLE vals2 (category VARCHAR(1), x INT);
INSERT INTO vals2 VALUES
('a',111),('a',110),('a',100),
('b','000'),('b',001),('b',011);
SELECT category, BIT_AND(x), BIT_OR(x), BIT_XOR(x)
FROM vals GROUP BY category;
+----------+------------+-----------+------------+
| category | BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+----------+------------+-----------+------------+
| a | 100 | 111 | 101 |
| b | 0 | 11 | 10 |
+----------+------------+-----------+------------+SELECT BIT_AND(NULL);
+----------------------+
| BIT_AND(NULL) |
+----------------------+
| 18446744073709551615 |
+----------------------+CREATE TABLE vals (x INT);
INSERT INTO vals VALUES(111),(110),(100);
SELECT BIT_AND(x), BIT_OR(x), BIT_XOR(x) FROM vals;
+------------+-----------+------------+
| BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+------------+-----------+------------+
| 100 | 111 | 101 |
+------------+-----------+------------+CREATE TABLE vals2 (category VARCHAR(1), x INT);
INSERT INTO vals2 VALUES
('a',111),('a',110),('a',100),
('b','000'),('b',001),('b',011);
SELECT category, BIT_AND(x), BIT_OR(x), BIT_XOR(x)
FROM vals GROUP BY category;
+----------+------------+-----------+------------+
| category | BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+----------+------------+-----------+------------+
| a | 100 | 111 | 101 |
| b | 0 | 11 | 10 |
+----------+------------+-----------+------------+SELECT BIT_XOR(NULL);
+---------------+
| BIT_XOR(NULL) |
+---------------+
| 0 |
+---------------+CREATE TABLE vals (x INT);
INSERT INTO vals VALUES(111),(110),(100);
SELECT BIT_AND(x), BIT_OR(x), BIT_XOR(x) FROM vals;
+------------+-----------+------------+
| BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+------------+-----------+------------+
| 100 | 111 | 101 |
+------------+-----------+------------+CREATE TABLE vals2 (category VARCHAR(1), x INT);
INSERT INTO vals2 VALUES
('a',111),('a',110),('a',100),
('b','000'),('b',001),('b',011);
SELECT category, BIT_AND(x), BIT_OR(x), BIT_XOR(x)
FROM vals GROUP BY category;
+----------+------------+-----------+------------+
| category | BIT_AND(x) | BIT_OR(x) | BIT_XOR(x) |
+----------+------------+-----------+------------+
| a | 100 | 111 | 101 |
| b | 0 | 11 | 10 |
+----------+------------+-----------+------------+SELECT BIT_OR(NULL);
+--------------+
| BIT_OR(NULL) |
+--------------+
| 0 |
+--------------+Count unique values. This function returns the number of distinct, non-NULL values found in the specified column or expression.
Calculate sample standard deviation. This function computes the standard deviation assuming the set of values represents a sample of the population.
CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
SELECT COUNT(*) FROM student;
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
SELECT COUNT(DISTINCT (name)) FROM student;
+------------------------+
| COUNT(DISTINCT (name)) |
+------------------------+
| 4 |
+------------------------+Calculate population standard deviation. This function returns the square root of the population variance. It is a synonym for STDDEV_POP().
STD(expr)Returns the population standard deviation of expr. This is an extension to standard SQL. The standard SQL function can be used instead.
It is an , and so can be used with the clause.
STD() can be used as a .
This function returns NULL if there were no matching rows.
As an :
As a :
(equivalent, standard SQL)
(equivalent, Oracle-compatible non-standard SQL)
(variance)
(sample standard deviation)
This page is licensed: GPLv2, originally from
Calculate sample variance. This function computes the statistical variance for a set of values assumed to be a sample of the population.
VAR_SAMP(expr)Returns the sample variance of expr. That is, the denominator is the number of rows minus one.
It is an , and so can be used with the clause.
VAR_SAMP() can be used as a .
VAR_SAMP() returns NULL if there were no matching rows.
As an :
As a :
(variance)
(population standard deviation)
This page is licensed: GPLv2, originally from
Perform calculations on multiple rows to return a single value. Includes standard SQL functions like SUM, AVG, COUNT, MIN, and MAX, often used with GROUP BY.
It is an aggregate function, and so can be used with the GROUP BY clause.
STDDEV() can be used as a window function.
This function returns NULL if there were no matching rows.
As an aggregate function:
As a window function:
STDDEV_POP (equivalent, standard SQL)
STD (equivalent, non-standard SQL)
VAR_POP (variance)
STDDEV_SAMP (sample standard deviation)
This page is licensed: GPLv2, originally from fill_help_tables.sql
CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
INSERT INTO stats VALUES
('a',1),('a',2),('a',3),
('b',11),('b',12),('b',20),('b',30),('b',60);
SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x)
FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a | 0.8165 | 1.0000 | 0.6667 |
| b | 18.0400 | 20.1693 | 325.4400 |
+----------+---------------+----------------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, STDDEV_POP(score)
OVER (PARTITION BY test) AS stddev_results FROM student_test;
+---------+--------+-------+----------------+
| name | test | score | stddev_results |
+---------+--------+-------+----------------+
| Chun | SQL | 75 | 16.9466 |
| Chun | Tuning | 73 | 24.1247 |
| Esben | SQL | 43 | 16.9466 |
| Esben | Tuning | 31 | 24.1247 |
| Kaolin | SQL | 56 | 16.9466 |
| Kaolin | Tuning | 88 | 24.1247 |
| Tatiana | SQL | 87 | 16.9466 |
+---------+--------+-------+----------------+CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
INSERT INTO stats VALUES
('a',1),('a',2),('a',3),
('b',11),('b',12),('b',20),('b',30),('b',60);
SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x)
FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a | 0.8165 | 1.0000 | 0.6667 |
| b | 18.0400 | 20.1693 | 325.4400 |
+----------+---------------+----------------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, VAR_SAMP(score)
OVER (PARTITION BY test) AS variance_results FROM student_test;
+---------+--------+-------+------------------+
| name | test | score | variance_results |
+---------+--------+-------+------------------+
| Chun | SQL | 75 | 382.9167 |
| Chun | Tuning | 73 | 873.0000 |
| Esben | SQL | 43 | 382.9167 |
| Esben | Tuning | 31 | 873.0000 |
| Kaolin | SQL | 56 | 382.9167 |
| Kaolin | Tuning | 88 | 873.0000 |
| Tatiana | SQL | 87 | 382.9167 |
+---------+--------+-------+------------------+STDDEV(expr)CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
INSERT INTO stats VALUES
('a',1),('a',2),('a',3),
('b',11),('b',12),('b',20),('b',30),('b',60);
SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x)
FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a | 0.8165 | 1.0000 | 0.6667 |
| b | 18.0400 | 20.1693 | 325.4400 |
+----------+---------------+----------------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, STDDEV_POP(score)
OVER (PARTITION BY test) AS stddev_results FROM student_test;
+---------+--------+-------+----------------+
| name | test | score | stddev_results |
+---------+--------+-------+----------------+
| Chun | SQL | 75 | 16.9466 |
| Chun | Tuning | 73 | 24.1247 |
| Esben | SQL | 43 | 16.9466 |
| Esben | Tuning | 31 | 24.1247 |
| Kaolin | SQL | 56 | 16.9466 |
| Kaolin | Tuning | 88 | 24.1247 |
| Tatiana | SQL | 87 | 16.9466 |
+---------+--------+-------+----------------+Find the maximum value. This function returns the highest value in a set of values, applicable to numbers, strings, and dates.
Returns the largest, or maximum, value of expr. MAX() can also take a string argument in which case it returns the maximum string value. The DISTINCT keyword can be used to find the maximum of the distinct values of expr, however, this produces the same result as omitting DISTINCT.
Note that and fields are currently compared by their string value rather than their relative position in the set, so MAX() may produce a different highest result than ORDER BY DESC.
It is an , and so can be used with the clause.
MAX() can be used as a .
MAX() returns NULL if there were no matching rows.
Not only ascending, but also can be used to optimize MAX.
Only ascending indexes can be used to optimize MAX.
MAX string:
Be careful to avoid this common mistake, not grouping correctly and returning mismatched data:
Difference between ORDER BY DESC and MAX():
As a :
(average)
(minimum)
(sum total)
returns the largest value from a list
This page is licensed: GPLv2, originally from
Concatenate strings from a group. This function joins non-NULL values from multiple rows into a single string, with an optional separator.
This function returns a string result with the concatenated non-NULL values from a group. If any expr in GROUP_CONCAT evaluates to NULL, that tuple is not present in the list returned by GROUP_CONCAT.
It returns NULL if all arguments are NULL, or there are no matching rows.
The maximum returned length in bytes is determined by the server system variable, which defaults to 1M.
If group_concat_max_len <= 512, the return type is or ; otherwise, the return type is or . The choice between binary or non-binary types depends from the input.
The full syntax is as follows:
DISTINCT eliminates duplicate values from the output string.
determines the order of returned values.
SEPARATOR specifies a separator between the values. The default separator is a comma (,). It is possible to avoid using a separator by specifying an empty string.
The clause can be used with GROUP_CONCAT.
Get a readable list of MariaDB users from the table:
In the former example, DISTINCT is used because the same user may occur more than once. The new line () used as a SEPARATOR makes the results easier to read.
Get a readable list of hosts from which each user can connect:
The former example shows the difference between the GROUP_CONCAT's (which sorts the concatenated hosts), and the SELECT's (which sorts the rows).
can be used with GROUP_CONCAT, so, for example, given the following table:
the following query:
can be more simply rewritten as:
NULLS:
This page is licensed: GPLv2, originally from
Calculate population standard deviation. This function computes the standard deviation assuming the set of values represents the entire population.
Returns the population standard deviation of expr (the square root of ). You can also use or , which are equivalent but not standard SQL.
It is an , and so can be used with the clause.
STDDEV_POP() can be used as a .
STDDEV_POP() returns NULL if there were no matching rows.
As an :
As a :
(equivalent, non-standard SQL)
(equivalent, Oracle-compatible non-standard SQL)
(variance)
(sample standard deviation)
This page is licensed: GPLv2, originally from
MAX([DISTINCT] expr)GROUP_CONCAT(expr)STDDEV_POP(expr)Variance is calculated by
working out the mean for the set;
for each number, subtracting the mean and squaring the result;
calculating the average of the resulting differences.
It is an aggregate function, and so can be used with the GROUP BY clause.
VARIANCE() can be used as a window function.
VARIANCE() returns NULL if there were no matching rows.
As an aggregate function:
As a window function:
VAR_POP (equivalent, standard SQL)
STDDEV_POP (population standard deviation)
STDDEV_SAMP (sample standard deviation)
This page is licensed: GPLv2, originally from fill_help_tables.sql
MIN()DISTINCTexprDISTINCTNote that SET and ENUM fields are currently compared by their string value rather than their relative position in the set, so MIN() may produce a different lowest result than ORDER BY ASC.
It is an aggregate function, and so can be used with the GROUP BY clause.
MIN() can be used as a window function.
MIN() returns NULL if there were no matching rows.
Not only ascending, but also descending indexes can be used to optimize MIN.
Only ascending indexes can be used to optimize MIN.
MIN() with a string:
Be careful to avoid this common mistake, not grouping correctly and returning mismatched data:
Difference between ORDER BY ASC and MIN():
As a window function:
This page is licensed: GPLv2, originally from fill_help_tables.sql
CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
SELECT name, MAX(score) FROM student GROUP BY name;
+---------+------------+
| name | MAX(score) |
+---------+------------+
| Chun | 75 |
| Esben | 43 |
| Kaolin | 88 |
| Tatiana | 87 |
+---------+------------+SELECT MAX(name) FROM student;
+-----------+
| MAX(name) |
+-----------+
| Tatiana |
+-----------+SELECT name,test,MAX(SCORE) FROM student;
+------+------+------------+
| name | test | MAX(SCORE) |
+------+------+------------+
| Chun | SQL | 88 |
+------+------+------------+CREATE TABLE student2(name CHAR(10),grade ENUM('b','c','a'));
INSERT INTO student2 VALUES('Chun','b'),('Esben','c'),('Kaolin','a');
SELECT MAX(grade) FROM student2;
+------------+
| MAX(grade) |
+------------+
| c |
+------------+
SELECT grade FROM student2 ORDER BY grade DESC LIMIT 1;
+-------+
| grade |
+-------+
| a |
+-------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, MAX(score)
OVER (PARTITION BY name) AS highest_score FROM student_test;
+---------+--------+-------+---------------+
| name | test | score | highest_score |
+---------+--------+-------+---------------+
| Chun | SQL | 75 | 75 |
| Chun | Tuning | 73 | 75 |
| Esben | SQL | 43 | 43 |
| Esben | Tuning | 31 | 43 |
| Kaolin | SQL | 56 | 88 |
| Kaolin | Tuning | 88 | 88 |
| Tatiana | SQL | 87 | 87 |
+---------+--------+-------+---------------+GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val]
[LIMIT {[offset,] row_count | row_count OFFSET offset}])SELECT student_name,
GROUP_CONCAT(test_score)
FROM student
GROUP BY student_name;SELECT GROUP_CONCAT(DISTINCT User ORDER BY User SEPARATOR '\n')
FROM mysql.user;SELECT User, GROUP_CONCAT(Host ORDER BY Host SEPARATOR ', ')
FROM mysql.user GROUP BY User ORDER BY User;CREATE TABLE d (dd DATE, cc INT);
INSERT INTO d VALUES ('2017-01-01',1);
INSERT INTO d VALUES ('2017-01-02',2);
INSERT INTO d VALUES ('2017-01-04',3);SELECT SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc)
ORDER BY cc DESC),",",1) FROM d;
+----------------------------------------------------------------------------+
| SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC),",",1) |
+----------------------------------------------------------------------------+
| 2017-01-04:3 |
+----------------------------------------------------------------------------+SELECT GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) FROM d;
+-------------------------------------------------------------+
| GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) |
+-------------------------------------------------------------+
| 2017-01-04:3 |
+-------------------------------------------------------------+CREATE OR REPLACE TABLE t1 (a int, b char);
INSERT INTO t1 VALUES (1, 'a'), (2, NULL);
SELECT GROUP_CONCAT(a, b) FROM t1;
+--------------------+
| GROUP_CONCAT(a, b) |
+--------------------+
| 1a |
+--------------------+CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
INSERT INTO stats VALUES
('a',1),('a',2),('a',3),
('b',11),('b',12),('b',20),('b',30),('b',60);
SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x)
FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a | 0.8165 | 1.0000 | 0.6667 |
| b | 18.0400 | 20.1693 | 325.4400 |
+----------+---------------+----------------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, STDDEV_POP(score)
OVER (PARTITION BY test) AS stddev_results FROM student_test;
+---------+--------+-------+----------------+
| name | test | score | stddev_results |
+---------+--------+-------+----------------+
| Chun | SQL | 75 | 16.9466 |
| Chun | Tuning | 73 | 24.1247 |
| Esben | SQL | 43 | 16.9466 |
| Esben | Tuning | 31 | 24.1247 |
| Kaolin | SQL | 56 | 16.9466 |
| Kaolin | Tuning | 88 | 24.1247 |
| Tatiana | SQL | 87 | 16.9466 |
+---------+--------+-------+----------------+VARIANCE(expr)CREATE TABLE v(i tinyint);
INSERT INTO v VALUES(101),(99);
SELECT VARIANCE(i) FROM v;
+-------------+
| VARIANCE(i) |
+-------------+
| 1.0000 |
+-------------+
INSERT INTO v VALUES(120),(80);
SELECT VARIANCE(i) FROM v;
+-------------+
| VARIANCE(i) |
+-------------+
| 200.5000 |
+-------------+CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
INSERT INTO stats VALUES
('a',1),('a',2),('a',3),
('b',11),('b',12),('b',20),('b',30),('b',60);
SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x)
FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a | 0.8165 | 1.0000 | 0.6667 |
| b | 18.0400 | 20.1693 | 325.4400 |
+----------+---------------+----------------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, VAR_POP(score)
OVER (PARTITION BY test) AS variance_results FROM student_test;
+---------+--------+-------+------------------+
| name | test | score | variance_results |
+---------+--------+-------+------------------+
| Chun | SQL | 75 | 287.1875 |
| Chun | Tuning | 73 | 582.0000 |
| Esben | SQL | 43 | 287.1875 |
| Esben | Tuning | 31 | 582.0000 |
| Kaolin | SQL | 56 | 287.1875 |
| Kaolin | Tuning | 88 | 582.0000 |
| Tatiana | SQL | 87 | 287.1875 |
+---------+--------+-------+------------------+MIN([DISTINCT] expr)CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
SELECT name, MIN(score) FROM student GROUP BY name;
+---------+------------+
| name | MIN(score) |
+---------+------------+
| Chun | 73 |
| Esben | 31 |
| Kaolin | 56 |
| Tatiana | 83 |
+---------+------------+SELECT MIN(name) FROM student;
+-----------+
| MIN(name) |
+-----------+
| Chun |
+-----------+SELECT name,test,MIN(score) FROM student;
+------+------+------------+
| name | test | MIN(score) |
+------+------+------------+
| Chun | SQL | 31 |
+------+------+------------+CREATE TABLE student2(name CHAR(10),grade ENUM('b','c','a'));
INSERT INTO student2 VALUES('Chun','b'),('Esben','c'),('Kaolin','a');
SELECT MIN(grade) FROM student2;
+------------+
| MIN(grade) |
+------------+
| a |
+------------+
SELECT grade FROM student2 ORDER BY grade ASC LIMIT 1;
+-------+
| grade |
+-------+
| b |
+-------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, MIN(score)
OVER (PARTITION BY name) AS lowest_score FROM student_test;
+---------+--------+-------+--------------+
| name | test | score | lowest_score |
+---------+--------+-------+--------------+
| Chun | SQL | 75 | 73 |
| Chun | Tuning | 73 | 73 |
| Esben | SQL | 43 | 31 |
| Esben | Tuning | 31 | 31 |
| Kaolin | SQL | 56 | 56 |
| Kaolin | Tuning | 88 | 56 |
| Tatiana | SQL | 87 | 87 |
+---------+--------+-------+--------------+Calculate the total sum. This function returns the sum of all values in a numeric expression, ignoring NULL values.
Returns the sum of expr. If the return set has no rows, SUM() returnsNULL. The DISTINCT keyword can be used to sum only the distinct values of expr.
SUM() can be used as a , although not with the DISTINCT specifier.
Commonly, SUM is used with a clause:
The clause is required when using an aggregate function along with regular column data, otherwise the result will be a mismatch, as in the following common type of mistake:
As a :
(average)
(maximum)
(minimum)
This page is licensed: GPLv2, originally from
SUM([DISTINCT] expr)CREATE TABLE sales (sales_value INT);
INSERT INTO sales VALUES(10),(20),(20),(40);
SELECT SUM(sales_value) FROM sales;
+------------------+
| SUM(sales_value) |
+------------------+
| 90 |
+------------------+
SELECT SUM(DISTINCT(sales_value)) FROM sales;
+----------------------------+
| SUM(DISTINCT(sales_value)) |
+----------------------------+
| 70 |
+----------------------------+CREATE TABLE sales (name CHAR(10), month CHAR(10), units INT);
INSERT INTO sales VALUES
('Chun', 'Jan', 75), ('Chun', 'Feb', 73),
('Esben', 'Jan', 43), ('Esben', 'Feb', 31),
('Kaolin', 'Jan', 56), ('Kaolin', 'Feb', 88),
('Tatiana', 'Jan', 87), ('Tatiana', 'Feb', 83);
SELECT name, SUM(units) FROM sales GROUP BY name;
+---------+------------+
| name | SUM(units) |
+---------+------------+
| Chun | 148 |
| Esben | 74 |
| Kaolin | 144 |
| Tatiana | 170 |
+---------+------------+SELECT name,SUM(units) FROM sales
;+------+------------+
| name | SUM(units) |
+------+------------+
| Chun | 536 |
+------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, SUM(score) OVER (PARTITION BY name) AS total_score FROM student_test;
+---------+--------+-------+-------------+
| name | test | score | total_score |
+---------+--------+-------+-------------+
| Chun | SQL | 75 | 148 |
| Chun | Tuning | 73 | 148 |
| Esben | SQL | 43 | 74 |
| Esben | Tuning | 31 | 74 |
| Kaolin | SQL | 56 | 144 |
| Kaolin | Tuning | 88 | 144 |
| Tatiana | SQL | 87 | 87 |
+---------+--------+-------+-------------+Calculate population variance. This function computes the statistical variance for a set of values assumed to be the entire population.
Returns the population standard variance of expr. It considers rows as the whole population, not as a sample, so it has the number of rows as the denominator. You can also use , which is equivalent but is not standard SQL.
Variance is calculated by
working out the mean for the set;
for each number, subtracting the mean and squaring the result;
calculating the average of the resulting differences.
It is an , and so can be used with the clause.
VAR_POP() can be used as a .
VAR_POP() returns NULL if there were no matching rows.
As an :
As a :
(equivalent, non-standard SQL)
(population standard deviation)
(sample standard deviation)
This page is licensed: GPLv2, originally from
VAR_POP(expr)CREATE TABLE v(i tinyint);
INSERT INTO v VALUES(101),(99);
SELECT VAR_POP(i) FROM v;
+------------+
| VAR_POP(i) |
+------------+
| 1.0000 |
+------------+
INSERT INTO v VALUES(120),(80);
SELECT VAR_POP(i) FROM v;
+------------+
| VAR_POP(i) |
+------------+
| 200.5000 |
+------------+CREATE OR REPLACE TABLE stats (category VARCHAR(2), x INT);
INSERT INTO stats VALUES
('a',1),('a',2),('a',3),
('b',11),('b',12),('b',20),('b',30),('b',60);
SELECT category, STDDEV_POP(x), STDDEV_SAMP(x), VAR_POP(x)
FROM stats GROUP BY category;
+----------+---------------+----------------+------------+
| category | STDDEV_POP(x) | STDDEV_SAMP(x) | VAR_POP(x) |
+----------+---------------+----------------+------------+
| a | 0.8165 | 1.0000 | 0.6667 |
| b | 18.0400 | 20.1693 | 325.4400 |
+----------+---------------+----------------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, VAR_POP(score)
OVER (PARTITION BY test) AS variance_results FROM student_test;
+---------+--------+-------+------------------+
| name | test | score | variance_results |
+---------+--------+-------+------------------+
| Chun | SQL | 75 | 287.1875 |
| Esben | SQL | 43 | 287.1875 |
| Kaolin | SQL | 56 | 287.1875 |
| Tatiana | SQL | 87 | 287.1875 |
| Chun | Tuning | 73 | 582.0000 |
| Esben | Tuning | 31 | 582.0000 |
| Kaolin | Tuning | 88 | 582.0000 |
+---------+--------+-------+------------------+Count rows or values. This function returns the number of rows that match criteria or the number of non-NULL values in an expression.
COUNT(expr)Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value. It is an , and so can be used with the clause.
COUNT(*) counts the total number of rows in a table.
COUNT() returns 0 if there were no matching rows.
COUNT() can be used as a .
example:
As a
This page is licensed: GPLv2, originally from
CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
SELECT COUNT(*) FROM student;
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+SELECT COUNT(DISTINCT (name)) FROM student;
+------------------------+
| COUNT(DISTINCT (name)) |
+------------------------+
| 4 |
+------------------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, COUNT(score) OVER (PARTITION BY name)
AS tests_written FROM student_test;
+---------+--------+-------+---------------+
| name | test | score | tests_written |
+---------+--------+-------+---------------+
| Chun | SQL | 75 | 2 |
| Chun | Tuning | 73 | 2 |
| Esben | SQL | 43 | 2 |
| Esben | Tuning | 31 | 2 |
| Kaolin | SQL | 56 | 2 |
| Kaolin | Tuning | 88 | 2 |
| Tatiana | SQL | 87 | 1 |
+---------+--------+-------+---------------+