SQL group (aggregate) functions operate on sets of values. If you use an aggregate function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.
General syntax for aggregate functions is:
SELECT "function type" ("column_name")
FROM "table_name";
The following are examples of aggregate functions:
AVG: Return the average value of the argument. (Does not work with temporal values unless first converted to numeric values.)
COUNT (DISTINCT): Return the count of a number of different values
COUNT: Return a count of the number of rows returned
GROUP CONCAT: Return a concatenated string
MAX and MIN: Return the maximum or minim values
The AVG function returns the average value for the specified column in a table. To find the average session time for users and GROUP BY last_name:
SELECT last_name, AVG(session_length)
-> FROM session_details GROUP BY last_name;
AVG() returns NULL if there are no matching rows.
Take the following “Nodes” table, where ‘nodes’ are user-contributed content:
NodeID | ContributionDate | NodeSize | NodePopularity | UserName |
---|---|---|---|---|
1 | 12/22/2010 | 160 | 2 | Smith |
2 | 08/10/2010 | 190 | 2 | Johnson |
3 | 07/13/2010 | 500 | 5 | Baldwin |
4 | 07/15/2010 | 420 | 2 | Smith |
5 | 12/22/2010 | 1000 | 4 | Wood |
6 | 10/2/2010 | 820 | 4 | Smith |
The SQL COUNT function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. If we want to count the number of nodes made by user Smith, we will use the following SQL COUNT expression:
SELECT COUNT * FROM Nodes
WHERE UserName = "Smith";
In the above statement, the COUNT keyword returns the number 3, because the user Smith has 3 total nodes.
If you don’t specify a WHERE clause when using the COUNT keyword, your statement will simply return the total number of rows in the table, which would be 6 in this example:
SELECT COUNT * FROM Nodes;
GROUP_CONCAT returns a string result with the concatenated non-NULL values from a group.
For example, without GROUP_CONCAT, this query:
SELECT id,client_id FROM services WHERE id = 3;
Returns:
id | client_id |
---|---|
3 | 7 |
3 | 8 |
3 | 9 |
But using GROUP_CONCAT in an alternate query:
SELECT id,GROUP_CONCAT(client_id) FROM services WHERE id = 3 GROUP BY id;
Will return:
id | GROUP_CONCAT(client_id) |
---|---|
3 | 7,8,9 |
MAX returns the maximum value in a group. In cases where MAX is passed a string argument, it will return the maximum string value.
MIN returns the minimum value of a group. Like MAX, MIN returns the minimum string value string value.
MAX and MIN return NULL if there are no matching rows.
SELECT product_id, MIN(price), MAX(price)
FROM inventory
GROUP BY product_id;
Note
For ENUM columns Drizzle uses the highest and lowest string values for MIN and MAX rather than relative position.