1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
Aggregate Functions
===================
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:
.. code-block:: mysql
SELECT "function type" ("column_name")
FROM "table_name";
The following are examples of aggregate functions:
:ref:`avg`: Return the average value of the argument. (Does not work with temporal values unless first converted to numeric values.)
:ref:`count`
(DISTINCT): Return the count of a number of different values
:ref:`count`: Return a count of the number of rows returned
:ref:`group_concat`: Return a concatenated string
:ref:`max`: Return the maximum or minim values
.. _avg:
AVG
---
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:
.. code-block:: mysql
SELECT last_name, AVG(session_length)
-> FROM session_details GROUP BY last_name;
AVG() returns NULL if there are no matching rows.
.. _count:
COUNT
-----
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:
.. code-block:: mysql
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:
.. code-block:: mysql
SELECT COUNT * FROM Nodes;
.. _group_concat:
GROUP CONCAT
-------------
GROUP_CONCAT returns a string result with the concatenated non-NULL values from a group.
For example, without GROUP_CONCAT, this query:
.. code-block:: mysql
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:
.. code-block:: mysql
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:
MAX and MIN
------------
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.
.. code-block:: mysql
SELECT product_id, MIN(price), MAX(price)
FROM inventory
GROUP BY product_id;
.. note::
For ``ENUM`` columns :program:`Drizzle` uses the highest and lowest string
values for ``MIN`` and ``MAX`` rather than relative position.
|