|
Custom Search
| |
|
Using Aggregate Functions and Group By Clause in MySQL Aggregate Functions are used to calculate summarized data such as an average or a sum. MySQL provides the common aggregate functions plus some MySQL specific ones. In this tutorial, we'll only cover the most often used aggregate functions. Note that all aggregate functions, with the exception of COUNT(*) function, ignore NULL values in the columns. When summarize values on a column, aggregate Functions can be used for the entire table or for groups of rows in the table. When applied to groups of rows, use GROUP BY clause with the SELECT statement. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
Practice #1-1: Using AVG function in orders table for the entire table. /*
Query result set - 1 row returned: Practice #1-2: Using AVG function in orders table for one customer. /*
Query result set - 1 row returned: Practice #1-3: Using AVG function in orders table for each customer. /*
Query result set - 89 rows returned: Practice #2-1: Using COUNT function. /*
Query result set - 9 rows returned: Practice #2-2: Using COUNT function on a column where NULL value exists. /*
Query result set - 8 rows returned: Practice #3-1: Using COUNT(DISTINCT) function. /*
Query result set - 1 row returned: Practice #3-2: Using COUNT(DISTINCT) function with NULL values. /*
Query 1 result set - 1 row returned:
Query 2 result set - 3 rows returned: Practice #4-1: Using COUNT(*) function. /*
Query result set - 9 rows returned: Practice #4-2: Using COUNT(*) function to count NULL values. /*
Query result set - 1 row returned: Practice #5: Using MAX() function. /*
Query result set - 1 row returned: Practice #6: Using MIN() function. /*
Query result set - 1 row returned: Practice #7: Using SUM() function. /*
Query result set - 1 row returned: Practice #8-1: Using GROUP_CONCAT() function. GROUP_CONCAT returns a concatenated string of non-NULL values for each group of rows in a table. Although GROUP_CONCAT is not an ANSI and an ISO standard function (which means it's an MySQL extension), it's very useful in the way that it saves you time to use another language such as PHP to loop and build a concatenated string by yourself. The full syntax of GROUP_CONCAT function is as follows: GROUP_CONCAT([DISTINCT] /*
Query result set - 8 rows returned: Practice #8-2: Using GROUP_CONCAT() function for distinct values. /*
Query result set - 8 row returned: Other tutorials in this category 1. More About GROUP BY Clause in MySQL |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |