--- description: Learn using MySQL Aggregate functions and its applications steps aggregate functions namely; SUM, AVG, MAX, MIN, COUNT, DISTINCT title: MySQL Aggregate Functions: SUM, COUNT, AVG & MAX image: https://www.guru99.com/images/mysql-aggregate-functions.png --- [Skip to content](#main) **⚡ Smart Summary** Aggregate Functions in MySQL perform a calculation across many rows of a single column and return one summarized value. The five ISO standard functions — COUNT, SUM, AVG, MIN, and MAX — power almost every report a database produces. * 🔢 **COUNT Behaviour:** COUNT(column) ignores NULL values, while COUNT(\*) counts every row in the table, including duplicates and NULLs. * 🚫 **DISTINCT Keyword:** DISTINCT removes duplicate values before the calculation runs; ALL is the default and keeps them. * 📉 **MIN and MAX:** MIN returns the smallest value in a column and MAX returns the largest, on numeric, string, and date types alike. * ➕ **SUM and AVG:** Both operate on numeric columns only, and both exclude NULL rows from the result returned. * 📊 **GROUP BY Pairing:** Adding GROUP BY turns a single summary figure into one summary row per group. * ⚠️ **NULL Trap:** AVG divides by the count of non-NULL rows only, so missing values silently raise the average. [ Read More ](javascript:void%280%29;) ![](https://www.guru99.com/images/mysql-aggregate-functions.png) ## What Are Aggregate Functions in MySQL? An **aggregate function** reads many rows of a single column and collapses them into one value. Aggregate functions are all about: * Performing calculations on multiple rows * Of a single column of a table * And returning a single value. The ISO standard defines five (5) aggregate functions, namely: 1. COUNT 2. SUM 3. AVG 4. MIN 5. MAX One rule applies to all five: **aggregate functions ignore NULL values**. COUNT(\*) is the single exception, and we look at why below. ## Why Use Aggregate Functions Different organization levels have different information requirements. Top level managers are usually interested in whole figures, not individual details. Aggregate functions allow us to easily produce summarized data from our database. For instance, from our myflix database, management may require the following reports: * Least rented movies. * Most rented movies. * Average number of times that each movie is rented out in a month. All of the above reports come from aggregate functions. Let’s look at each in detail. ## COUNT function The COUNT function returns the total number of values in the specified field, on both numeric and non-numeric data types. **Like every aggregate function, COUNT(column) excludes NULL values.** COUNT(\*) is a special form that returns the count of all rows in a table. It also counts [NULLs](https://www.guru99.com/null.html) and duplicates, because it counts rows rather than values. The movierentals table holds this data: | reference\_ number | transaction\_ date | return\_date | membership\_ number | movie\_id | movie\_ returned | | ------------------ | ------------------ | ------------ | ------------------- | --------- | ---------------- | | 11 | 20-06-2012 | NULL | 1 | 1 | 0 | | 12 | 22-06-2012 | 25-06-2012 | 1 | 2 | 0 | | 13 | 22-06-2012 | 25-06-2012 | 3 | 2 | 0 | | 14 | 21-06-2012 | 24-06-2012 | 2 | 2 | 0 | | 15 | 23-06-2012 | NULL | 3 | 3 | 0 | Let’s suppose that we want to get the number of times that the movie with id 2 has been rented out. SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2; Executing this in [MySQL Workbench](https://www.guru99.com/introduction-to-mysql-workbench.html) against myflixdb returns 3, because three rows carry movie\_id 2. | COUNT(\`movie\_id\`) | | -------------------- | | 3 | ### RELATED ARTICLES * [MySQL UNION – Complete Tutorial ](https://www.guru99.com/unions.html "MySQL UNION – Complete Tutorial") * [MySQL SubQuery with Examples ](https://www.guru99.com/sub-queries.html "MySQL SubQuery with Examples") * [MySQL DELETE Query: How to Delete a Row from Table ](https://www.guru99.com/delete-and-update.html "MySQL DELETE Query: How to Delete a Row from Table") * [MySQL UPDATE Query with Example ](https://www.guru99.com/sql-update-query.html "MySQL UPDATE Query with Example") ## DISTINCT Keyword COUNT answers “how many”. The next question is usually “how many _different_ ones”, and that is what DISTINCT is for. [](https://www.guru99.com/images/DistinctApple.png) The DISTINCT keyword omits duplicates from our results by grouping identical values together, exactly as the illustration above suggests. First, let’s execute a simple query. SELECT `movie_id` FROM `movierentals`; | movie\_id | | --------- | | 1 | | 2 | | 2 | | 2 | | 3 | Now the same query with the DISTINCT keyword: SELECT DISTINCT `movie_id` FROM `movierentals`; DISTINCT omits the duplicate records: | movie\_id | | --------- | | 1 | | 2 | | 3 | ## COUNT vs COUNT(\*) vs COUNT(DISTINCT): Which One Should You Use? DISTINCT can also be placed _inside_ an aggregate function, and this is where most beginners lose track of which rows are actually counted. The four forms below all run against the same five-row movierentals table shown earlier, yet they do not all return the same number. The difference comes down to two questions: does the form count rows or values, and does it keep duplicates? | Form | What it counts | Result on movierentals | | ----------------------------- | --------------------------------------------------------------- | ---------------------- | | COUNT(\*) | Every row, including duplicates and rows that are entirely NULL | 5 | | COUNT(\`movie\_id\`) | Every non-NULL value in the column, duplicates included | 5 | | COUNT(\`return\_date\`) | Non-NULL values only — the two NULL return dates are skipped | 3 | | COUNT(DISTINCT \`movie\_id\`) | Unique non-NULL values only | 3 | SELECT COUNT(*) AS `all_rows`, COUNT(`return_date`) AS `returned_rows`, COUNT(DISTINCT `movie_id`) AS `unique_movies` FROM `movierentals`; > **💡 Tip:** Use COUNT(\*) for a row count, COUNT(column) when a NULL should mean “does not apply”, and COUNT(DISTINCT column) for unique values. The opposite of DISTINCT is ALL — the default, and therefore rarely written out. ## MIN function The MIN function **returns the smallest value in the specified table field**. Suppose we want the year in which the oldest movie in our library was released. MySQL’s MIN function gives us that. SELECT MIN(`year_released`) FROM `movies`; Result: | MIN(\`year\_released\`) | | ----------------------- | | 2005 | ## MAX function Just as the name suggests, the MAX function is the opposite of the MIN function. It **returns the largest value from the specified table field**. Suppose we want the year in which the latest movie in our database was released. The following example returns it. SELECT MAX(`year_released`) FROM `movies`; Result: | MAX(\`year\_released\`) | | ----------------------- | | 2012 | ## SUM function MIN and MAX pick an existing value from a column. SUM and AVG compute a new number from the whole column. Suppose we want the total amount of payments made so far. The MySQL **SUM** function **returns the sum of all values in the specified column**. **SUM works on numeric fields only**, and **NULL values are excluded from the result**. The following table shows the data in the payments table. | payment\_ id | membership\_ number | payment\_ date | description | amount\_ paid | external\_ reference \_number | | ------------ | ------------------- | -------------- | -------------------- | ------------- | ----------------------------- | | 1 | 1 | 23-07-2012 | Movie rental payment | 2500 | 11 | | 2 | 1 | 25-07-2012 | Movie rental payment | 2000 | 12 | | 3 | 3 | 30-07-2012 | Movie rental payment | 6000 | NULL | The query shown below gets all the payments made and sums them up into a single result: 2500 + 2000 + 6000 = 10500. SELECT SUM(`amount_paid`) FROM `payments`; Result: | SUM(\`amount\_paid\`) | | --------------------- | | 10500 | ## AVG function The MySQL AVG function **returns the average of the values in a specified column**. Just like the SUM function, it **works only on numeric data types**. Suppose we want to find the average amount paid. We can use the following query, which divides the total of 10500 by the three non-NULL payment rows. SELECT AVG(`amount_paid`) FROM `payments`; Result: | AVG(\`amount\_paid\`) | | --------------------- | | 3500 | > **⚠️ Warning:** AVG divides by the number of non-NULL rows, not by the row count of the table. A NULL amount is skipped rather than counted as zero, which quietly pushes the average up. Use AVG(IFNULL(\`amount\_paid\`, 0)) when a missing value means zero. ## Practical Example: Combining Aggregate Functions with GROUP BY Each function above returned one figure for the whole table. Adding a [GROUP BY](https://www.guru99.com/group-by.html) clause returns one figure _per group_ instead — and that is how real reports are built. The following example groups members by name, then counts the total number of payments, the average payment amount, and the grand total of the payment amounts for each member. SELECT m.`full_names`, COUNT(p.`payment_id`) AS `paymentscount`, AVG(p.`amount_paid`) AS `averagepaymentamount`, SUM(p.`amount_paid`) AS `totalpayments` FROM members m, payments p WHERE m.`membership_number` = p.`membership_number` GROUP BY m.`full_names`; Executing the above example in MySQL Workbench gives us the following results. [](https://www.guru99.com/images/practical%5Fexample.png) The query joins the two tables in the WHERE clause — the older comma-join style. Modern code writes the same logic as an explicit [INNER JOIN … ON](https://www.guru99.com/joins.html). Note also that every non-aggregated column in the SELECT list must appear in GROUP BY, or MySQL 5.7 and later reject the query under ONLY\_FULL\_GROUP\_BY. See the [official MySQL aggregate function reference](https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html). ## FAQs 🔍 What is the difference between WHERE and HAVING when filtering aggregates? The [WHERE clause](https://www.guru99.com/where-clause.html) filters individual rows before the aggregate is calculated. HAVING filters the grouped results afterwards, so only HAVING can reference an aggregate such as COUNT(\*) or SUM(amount\_paid). 📋 Can aggregate functions be used without a GROUP BY clause? Yes. Without GROUP BY, the aggregate treats the entire result set as one group and returns exactly one row. Adding GROUP BY splits that result into one row for each distinct group value. 🧮 Do MIN and MAX work on text and date columns? Yes. Unlike SUM and AVG, MIN and MAX work on any comparable type. On a text column they return the alphabetically first and last values, and on a date column the earliest and latest dates. 🤖 Can AI tools write aggregate SQL queries from a plain-English question? Yes. Text-to-SQL assistants translate questions such as “average payment per member” into a GROUP BY query. Run the generated SQL in [MySQL Workbench](https://www.guru99.com/introduction-to-mysql-workbench.html) and check the row counts before trusting the numbers. 🧠 Why do AI-generated aggregate queries often return the wrong totals? The usual cause is NULL handling and duplicated join rows. An AI model may pick COUNT(\*) where COUNT(column) is needed, or join a table twice, which inflates every SUM. Always verify against a known figure. #### Summarize this post with: ChatGPT Perplexity Grok Google AI **Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** ##### Sign up for the newsletter Subscribe for Free You have successfully subscribed. Please check your inbox. ![AI-Newsletter]() Chosen by over **350,000+** professionals [Scroll to top ](#wrapper)Scroll to top × Toggle Menu Close Search for: Search ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/mysql-aggregate-functions.png","url":"https://www.guru99.com/images/mysql-aggregate-functions.png","width":"700","height":"250","caption":"MySQL Aggregate Functions","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/aggregate-functions.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/sql","name":"SQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/aggregate-functions.html","name":"MySQL Aggregate Functions: SUM, COUNT, AVG & MAX"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/aggregate-functions.html#webpage","url":"https://www.guru99.com/aggregate-functions.html","name":"MySQL Aggregate Functions: SUM, COUNT, AVG & MAX","dateModified":"2026-07-14T11:24:30+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/mysql-aggregate-functions.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/aggregate-functions.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/marcus","name":"Marcus Allen","description":"I'm Marcus Allen, an SQL and Data Warehousing Consultant with over a decade of experience in designing and optimizing large-scale data solutions.","url":"https://www.guru99.com/author/marcus","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/marcus-allen-author.png","url":"https://www.guru99.com/images/marcus-allen-author.png","caption":"Marcus Allen","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"SQL","headline":"MySQL Aggregate Functions: SUM, COUNT, AVG & MAX","description":"Learn using MySQL Aggregate functions and its applications steps aggregate functions namely; SUM, AVG, MAX, MIN, COUNT, DISTINCT","keywords":"sql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/marcus","name":"Marcus Allen"},"dateModified":"2026-07-14T11:24:30+05:30","image":{"@id":"https://www.guru99.com/images/mysql-aggregate-functions.png"},"copyrightYear":"2026","name":"MySQL Aggregate Functions: SUM, COUNT, AVG & MAX","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"What is the difference between WHERE and HAVING when filtering aggregates?","acceptedAnswer":{"@type":"Answer","text":"The WHERE clause filters individual rows before the aggregate is calculated. HAVING filters the grouped results afterwards, so only HAVING can reference an aggregate such as COUNT(*) or SUM(amount_paid)."}},{"@type":"Question","name":"Can aggregate functions be used without a GROUP BY clause?","acceptedAnswer":{"@type":"Answer","text":"Yes. Without GROUP BY, the aggregate treats the entire result set as one group and returns exactly one row. Adding GROUP BY splits that result into one row for each distinct group value."}},{"@type":"Question","name":"Do MIN and MAX work on text and date columns?","acceptedAnswer":{"@type":"Answer","text":"Yes. Unlike SUM and AVG, MIN and MAX work on any comparable type. On a text column they return the alphabetically first and last values, and on a date column the earliest and latest dates."}},{"@type":"Question","name":"Can AI tools write aggregate SQL queries from a plain-English question?","acceptedAnswer":{"@type":"Answer","text":"Yes. Text-to-SQL assistants translate questions such as \"average payment per member\" into a GROUP BY query. Run the generated SQL in MySQL Workbench and check the row counts before trusting the numbers."}},{"@type":"Question","name":"Why do AI-generated aggregate queries often return the wrong totals?","acceptedAnswer":{"@type":"Answer","text":"The usual cause is NULL handling and duplicated join rows. An AI model may pick COUNT(*) where COUNT(column) is needed, or join a table twice, which inflates every SUM. Always verify against a known figure."}}]}],"@id":"https://www.guru99.com/aggregate-functions.html#schema-1143574","isPartOf":{"@id":"https://www.guru99.com/aggregate-functions.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/aggregate-functions.html#webpage"}}]} ```