Write an Article?
Ask Question

MySQL Aggregate Functions

Previous Page
Next Page

We can use Aggregate functions to perform calculations and to reduce many rows to a single row. We use the GROUP by function and the Having clause to filter records when we have aggregate functions. The Aggregate functions allowed are:

  • Sum
  • Count
  • Max
  • Min
  • Group_concat
  • First
  • Last

The functions are self-explanatory and so we will see examples of each.

Sum() function in MySQL

The sum() functions add together all the values from the records for the column specified. If the sum function is applied on a non-number column the sum is returned as 0.

To get the sum of all the amounts in the payment table, we do

SELECT SUM(AMOUNT) FROM PAYMENT;

Count() function in MySQL

The count function counts the number of records present in the Result Set. IF we do a count without any conditions i.e. no Having Clause, all the records in the table are counted.

The Count() function gives the same result no matter what we put inside the count(). It is best practice to put the name of the column we are counting.

SELECT COUNT(CUSTOMER_ID) FROM CUSTOMER WHERE STORE_ID = 2;

AVG() function in MySQL

The AVG function is for retrieving the average value. When using the AVG to group a column, we use the GROUP BY function.

SELECT AVG(AMOUNT),CUSTOMER_ID
FROM PAYMENT
GROUP BY CUSTOMER_ID;

AVG() with the Having clause in MySQL

We can use aggregate functions inside the Having clause also.

SELECT C.CUSTOMER_ID,C.FIRST_NAME,C.LAST_NAME
FROM PAYMENT P,CUSTOMER C
WHERE P.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
HAVING AVG(P.AMOUNT) > 3
LIMIT 20;

Max() function in MySQL

The MAX gives the maximum value out of all the records in that column.

select MAX(AMOUNT) from PAYMENT;

MIN() function in MySQL

Similar to MAX, the MIN gives the minimum value present

select MIN(AMOUNT) from PAYMENT;

GROUP_CONCAT example in MySQL

The Group_concat function virtually groups and combines multiple rows into a single row. For example, the below Query to get all the actors for documentary movies

SELECT F.TITLE,F.DESCRIPTION,F.LENGTH,F.RATING,GROUP_CONCAT(CONCAT(A.FIRST_NAME,A.LAST_NAME) SEPARATOR ', ') AS `ACTORS`
FROM FILM_ACTOR FA, FILM F ,ACTOR A
WHERE F.FILM_ID = FA.FILM_ID
AND A.ACTOR_ID = FA.ACTOR_ID
AND F.FILM_ID IN (
SELECT FILM_ID FROM FILM_CATEGORY, CATEGORY
WHERE CATEGORY.CATEGORY_ID = FILM_CATEGORY.CATEGORY_ID
AND CATEGORY.CATEGORY_ID = 6)
GROUP BY F.FILM_ID;

Previous Page
Next Page