1994.4.52
by Marisa Plumb
modifying folder structure |
1 |
Aggregate Functions
|
2201.1.2
by Olaf van der Spek
Fix EOL |
2 |
===================
|
1994.4.52
by Marisa Plumb
modifying folder structure |
3 |
|
4 |
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. |
|
5 |
||
2165.2.5
by Andrew Hutchings
Make fixes to temporal docs |
6 |
General syntax for aggregate functions is: |
7 |
||
8 |
.. code-block:: mysql |
|
1994.4.52
by Marisa Plumb
modifying folder structure |
9 |
|
10 |
SELECT "function type" ("column_name") |
|
11 |
FROM "table_name"; |
|
2201.1.2
by Olaf van der Spek
Fix EOL |
12 |
|
13 |
The following are examples of aggregate functions: |
|
1994.4.91
by Marisa Plumb
adding aggregate function content |
14 |
|
15 |
:ref:`avg`: Return the average value of the argument. (Does not work with temporal values unless first converted to numeric values.) |
|
16 |
||
17 |
:ref:`count` |
|
1994.4.52
by Marisa Plumb
modifying folder structure |
18 |
(DISTINCT): Return the count of a number of different values |
19 |
||
1994.4.91
by Marisa Plumb
adding aggregate function content |
20 |
:ref:`count`: Return a count of the number of rows returned |
1994.4.52
by Marisa Plumb
modifying folder structure |
21 |
|
1994.4.91
by Marisa Plumb
adding aggregate function content |
22 |
:ref:`group_concat`: Return a concatenated string |
23 |
||
24 |
:ref:`max`: Return the maximum or minim values |
|
25 |
||
26 |
||
27 |
.. _avg: |
|
28 |
||
29 |
AVG
|
|
30 |
---
|
|
31 |
||
32 |
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: |
|
33 |
||
34 |
.. code-block:: mysql |
|
35 |
||
36 |
SELECT last_name, AVG(session_length) |
|
37 |
-> FROM session_details GROUP BY last_name; |
|
38 |
||
39 |
AVG() returns NULL if there are no matching rows. |
|
40 |
||
41 |
||
42 |
.. _count: |
|
43 |
||
44 |
COUNT
|
|
45 |
-----
|
|
46 |
||
47 |
Take the following "Nodes" table, where 'nodes' are user-contributed content: |
|
48 |
||
49 |
+--------+-------------------+------------+----------------+-------------------+ |
|
50 |
|NodeID |ContributionDate |NodeSize |NodePopularity |UserName | |
|
51 |
+========+===================+============+================+===================+ |
|
52 |
|1 |12/22/2010 |160 |2 |Smith | |
|
53 |
+--------+-------------------+------------+----------------+-------------------+ |
|
54 |
|2 |08/10/2010 |190 |2 |Johnson | |
|
55 |
+--------+-------------------+------------+----------------+-------------------+ |
|
56 |
|3 |07/13/2010 |500 |5 |Baldwin | |
|
57 |
+--------+-------------------+------------+----------------+-------------------+ |
|
58 |
|4 |07/15/2010 |420 |2 |Smith | |
|
59 |
+--------+-------------------+------------+----------------+-------------------+ |
|
60 |
|5 |12/22/2010 |1000 |4 |Wood | |
|
61 |
+--------+-------------------+------------+----------------+-------------------+ |
|
62 |
|6 |10/2/2010 |820 |4 |Smith | |
|
63 |
+--------+-------------------+------------+----------------+-------------------+ |
|
64 |
||
65 |
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: |
|
66 |
||
67 |
.. code-block:: mysql |
|
68 |
||
69 |
SELECT COUNT * FROM Nodes |
|
70 |
WHERE UserName = "Smith"; |
|
71 |
||
72 |
In the above statement, the COUNT keyword returns the number 3, because the user Smith has 3 total nodes. |
|
73 |
||
74 |
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: |
|
75 |
||
76 |
.. code-block:: mysql |
|
77 |
||
78 |
SELECT COUNT * FROM Nodes; |
|
79 |
||
80 |
||
81 |
.. _group_concat: |
|
82 |
||
83 |
GROUP CONCAT
|
|
84 |
-------------
|
|
85 |
||
86 |
GROUP_CONCAT returns a string result with the concatenated non-NULL values from a group. |
|
87 |
||
88 |
For example, without GROUP_CONCAT, this query: |
|
89 |
||
90 |
.. code-block:: mysql |
|
91 |
||
92 |
SELECT id,client_id FROM services WHERE id = 3; |
|
93 |
||
94 |
Returns: |
|
95 |
||
96 |
+----+-----------+ |
|
97 |
| id | client_id |
|
|
98 |
+====+===========+ |
|
99 |
| 3 | 7 |
|
|
100 |
+----+-----------+ |
|
101 |
| 3 | 8 |
|
|
102 |
+----+-----------+ |
|
103 |
| 3 | 9 |
|
|
104 |
+----+-----------+ |
|
105 |
||
106 |
But using GROUP_CONCAT in an alternate query: |
|
107 |
||
108 |
.. code-block:: mysql |
|
109 |
||
110 |
SELECT id,GROUP_CONCAT(client_id) FROM services WHERE id = 3 GROUP BY id; |
|
111 |
||
112 |
Will return: |
|
113 |
||
114 |
+----+-------------------------+ |
|
115 |
| id | GROUP_CONCAT(client_id) |
|
|
116 |
+====+=========================+ |
|
117 |
| 3 | 7,8,9 |
|
|
118 |
+----+-------------------------+ |
|
119 |
||
120 |
||
121 |
.. _max: |
|
122 |
||
123 |
MAX and MIN
|
|
124 |
------------
|
|
125 |
||
126 |
MAX returns the maximum value in a group. In cases where MAX is passed a string argument, it will return the maximum string value. |
|
127 |
||
128 |
MIN returns the minimum value of a group. Like MAX, MIN returns the minimum string value string value. |
|
129 |
||
130 |
MAX and MIN return NULL if there are no matching rows. |
|
131 |
||
132 |
.. code-block:: mysql |
|
133 |
||
134 |
SELECT product_id, MIN(price), MAX(price) |
|
135 |
FROM inventory |
|
136 |
GROUP BY product_id; |
|
137 |
||
2194.5.3
by Andrew Hutchings
Markup fixes |
138 |
.. note:: |
139 |
||
140 |
For ``ENUM`` columns :program:`Drizzle` uses the highest and lowest string |
|
141 |
values for ``MIN`` and ``MAX`` rather than relative position. |