MySQL Aggregate functions and grouping

Transcript

Aggregate functions and grouping in MySQL

Sample Table : invoice

invoice_no  book_id  pub_lang  qty  rate  cost
INV0001     BK001    English    15   75   1125
INV0002     BK004    English    8    55   440
INV0003     BK005    NULL       20   20   400
INV0004     BK004    English    15   35   525
INV0005     BK001    English    8    25   200
INV0006     BK003    Hindi      20   45   900

MySQL AVG() function

MySQL AVG() function retrieves the average value of a given expression. If the function does not find a matching row, it returns NULL.

Syntax : AVG([DISTINCT] expr)

Example : SELECT AVG(cost) AS "Average Cost" FROM invoice;

Output :

 Average Cost
-------------------
598.333333

MySQL BIT_AND() function

MySQL BIT_AND() function returns the bitwise AND of all bits in a given expression.The calculation is performed on 64 bit precession.

Syntax : BIT_AND(expr)

Example : SELECT 2 & 15;

Output :

2 & 15
----------
2

MySQL BIT_OR() function

MySQL BIT_OR() function returns the bitwise OR of all bits in a given expression.
The calculation is performed on 64 bit precession.
If this function does not find a matching row, it returns 0.

Syntax : BIT_OR(expr)

Example : SELECT 2 | 5;

Output :

2 | 5
-------
7

MySQL BIT_XOR() function

MySQL BIT_XOR() function returns the bitwise XOR of all bits in a given expression. The calculation is performed on 64 bit precession.

Syntax : BIT_XOR(expr)

Example : SELECT 2 ^ 15;

Output :

2 ^ 15
-----------
13

MySQL COUNT() function

MySQL COUNT() function returns a count of number of non-NULL values of a given expression. If it does not find any matching row, it returns 0.

Syntax : COUNT(expr);

Example : SELECT COUNT(*) FROM invoice;

Output :

COUNT(*)
--------------
6

MySQL COUNT() function

Example : SELECT pub_lang, COUNT(pub_lang), COUNT(DISTINCT(pub_lang)) FROM invoice GROUP BY pub_lang;

Output :

pub_lang       COUNT(pub_lang)               COUNT(DISTINCT(pub_lang))
------------------------------------------------------------------------------------------
NULL                   0                                       0
English                4                                       1
Hindi                  1                                       1

MySQL GROUP_CONCAT() function

MySQL GROUP_CONCAT() function returns a string with concatenated non- NULL value from a group. Returns NULL when there are no non-NULL values.

Syntax : GROUP_CONCAT(expr);

Example : SELECT pub_lang,GROUP_CONCAT(book_id) FROM invoice GROUP BY pub_lang;

Output :

 pub_lang                    GROUP_CONCAT(book_id)
----------------------------------------------------------
NULL                                  BK005
English                               BK001,BK004,BK004,BK001
Hindi                                 BK003

MySQL MAX() function

MySQL MAX() function returns the maximum value of an expression.

Syntax : MAX(expr);

Example : SELECT MAX(rate) FROM invoice;

Output :

MAX(rate)
-----------
75.00

MySQL MAX() function

Example : SELECT pub_lang,MAX(qty) FROM invoice
GROUP BY pub_lang HAVING MAX(qty)>=8;

Output :

pub_lang         MAX(qty)
--------------------------
NULL                  20
English               15
Hindi                 20

MySQL MIN() function

MySQL MIN() function returns the minimum value of an expression. MIN() function returns NULL when the return set has no rows.

Syntax : MIN(expr);

Example : SELECT MIN(rate) FROM invoice;

MIN(rate)
-----------
20.00

MySQL STD() function

MySQL STD() function returns the population standard deviation of expression.
It returns NULL if no matching row is found.

Syntax : STD(expr);

Example : SELECT STD(cost) FROM invoice;

Output :

STD(cost)
------------------
315.392172

MySQL STDDEV_POP() function

MySQL STDDEV_POP() function returns the population standard deviation of an expression ( the square root of VAR_POP()).
It returns NULL if no matching row is found.

Syntax : STDDEV_POP(expr);

Example : SELECT STDDEV_POP(cost) FROM invoice;

Output :

STDDEV_POP(cost)
------------------
315.392172

MySQL STDDEV_SAMP() function

MySQL STDDEV_SAMP() function returns the sample standard deviation of an expression ( the square root of VAR_SAMP()).

It returns NULL if no matching rows are found.

Syntax : STDDEV_SAMP(expr);

Example : SELECT STDDEV_SAMP(cost) FROM invoice;

Output :

STDDEV_SAMP(cost)
------------------------------
345.494814

MySQL STDDEV() function

MySQL STDDEV() function returns the population standard deviation of expression.

The STDDEV() function is used to calculate statistical information for a specified numeric field in a query. It returns NULL if no matching rows found.

Syntax : STDDEV(expr);

Example : SELECT STDDEV(cost) FROM invoice;

Output :

STDDEV(cost)
--------------------
315.392172

MySQL SUM() function

MySQL SUM() function returns the sum of an expression. SUM() function returns NULL when the return set has no rows.

Syntax : SUM([DISTINCT] expr)

Example : SELECT SUM(cost) FROM invoice;,/

Output :

SUM(cost)
-------------
3590.00

MySQL SUM() function

Example : SELECT book_id,SUM(cost) FROM invoice GROUP BY book_id;

Output :

book_id SUM(cost)
-----------------------
BK001    1325.00
BK003    900.00
BK004    965.00
BK005    400.00

MySQL VAR_POP() function

MySQL VAR_POP() function returns the population standard variance of an expression.

Syntax : VAR_POP(expr)

Example : SELECT VAR_POP(cost) FROM invoice;

Output :

VAR_POP(cost)
----------------------
99472.222222

MySQL VAR_SAMP() function

MySQL VAR_SAMP() function returns the sample variance of an given expression.

Syntax : VAR_SAMP(expr)

Example : SELECT VAR_SAMP(cost) FROM invoice;

Output :

VAR_SAMP(cost)
-------------------------
119366.666667

MySQL VARIANCE() function

MySQL VARIANCE() function returns the population standard variance of an expression.

Syntax : VARIANCE(expr)

Example : SELECT VARIANCE(cost) FROM invoice;

Output :

VARIANCE(cost)
----------------------
99472.222222