SQL Aggregate Functions
SQL Aggregate functions is use to perform some calculation by aggregating and making groups of certain similar records.
Function | Explanation |
|---|---|
COUNT() | It is use to count the number of records in a table. |
SUM() | It is use to sum all the records present in a table. |
AVG() | It is use to give the average value of all the records in a table. |
MAX() | It is use to give the maximum value in a table. |
MIN() | It is use to give the minimum value in a record. |
COUNT()
-
It is use to count the number of records present in a table.
-
NULL values are not consider by default.
-
Can be use to count distinct values using COUNT(DISTINCT name).
-
It can count numeric as well as non-numeric data.
-
We can use COUNT(*) to count all the records and it will consider null values as well and it is special feature present only in count function.
-
There should not be any space between count and parenthesis.
Syntax
SELECT
COUNT(name)
FROM
table_name
WHERE
conditions;
SUM()
-
It is use to sum all the records present in a table.
-
It can SUM only numeric data.
-
We can't use SUM(*).
-
There should not be any space between SUM and parenthesis.
Syntax
SELECT
SUM(name)
FROM
table_name
WHERE
conditions;
MAX()
-
It is use to get the maximum record present in a table.
-
It can be applied to numeric data and non-numeric data.
-
We can't use MAX(*).
-
There should not be any space between MAX and parenthesis.
Syntax
SELECT
MAX(name)
FROM
table_name
WHERE
conditions;
MIN()
-
It is use to get the minimum record present in a table.
-
It can be applied to numeric data and non-numeric data.
-
We can't use MIN(*).
-
There should not be any space between MIN and parenthesis.
Syntax
SELECT
MIN(name)
FROM
table_name
WHERE
conditions;
AVG()
-
It is use to get the average of all the records present in a table.
-
It can be applied to only numeric data.
-
We can't use AVG(*).
-
There should not be any space between AVG and parenthesis.
Syntax
SELECT
AVG(name)
FROM
table_name
WHERE
conditions;
ROUND()
-
It is use to round of the given value.
-
It can round of only numeric value.
-
The decimal numbers are optional parameter in a round function.
-
There should not be any space between round and parenthesis.
Syntax
SELECT
ROUND(Value, Decimal_numbers)
FROM
table_name
WHERE
conditions;
IFNULL()
-
IFNULL function have exactly two parameters.
-
If the first value is null then it will print the second value.
-
If both the values are null then it will return null.
-
It can't have more than two values.
Syntax
SELECT
column_1, IFNULL(column_2,'new_value')
FROM
table_name
WHERE
conditions;
COALESCE()
-
COALESCE function can have n number of parameters.
-
It will give the first not null value starting from the left parameter and goes to the right.
-
If all the values are null then it will return null.
-
It can't have more than two values.
Syntax
SELECT
column_1, IFNULL(column_2,'new_value')
FROM
table_name
WHERE
conditions;
GROUP BY
-
GROUP BY Clause is used to group the results according to certain values.
-
It is very useful when used with aggregated functions.
-
It is recommended to have the column name in select clause which is used in the group by clause.
Syntax
SELECT
column_1, count(column_1)
FROM
table_name
WHERE
conditions
GROUP BY column_1
ORDER BY column_1;
HAVING
-
Having is used to refine and fulfil certain conditions like where clause.
-
Having can be used with group by clause.
-
Having can have aggregated conditions. where clause can't have aggregated functions.
-
but we should not use non aggregated and aggregated condition together in having.
-
Whenever we encounter problems where we have to count how many times certain things occur or we want to use aggregate function that time we should use having.