Oracle 5.0, Reference Manual

Page: 990 / 3232

GROUP BY
(Aggregate) Functions
970
This function returns
0
if there were no matching rows.
•
COUNT(expr)
[970]
Returns a count of the number of non-
NULL
values of
expr
in the rows retrieved by a
SELECT
statement. The result is a
BIGINT
value.
COUNT()
[970] returns
0
if there were no matching rows.
mysql> SELECT student.student_name,COUNT(*)
-> FROM student,course
-> WHERE student.student_id=course.student_id
-> GROUP BY student_name;
COUNT(*)
[970] is somewhat different in that it returns a count of the number of rows retrieved,
whether or not they contain
NULL
values.
COUNT(*)
[970] is optimized to return very quickly if the
SELECT
retrieves from one table, no other
columns are retrieved, and there is no
WHERE
clause. For example:
mysql> SELECT COUNT(*) FROM student;
This optimization applies only to
MyISAM
tables only, because an exact row count is stored for
this storage engine and can be accessed very quickly. For transactional storage engines such as
InnoDB
and
BDB
, storing an exact row count is more problematic because multiple transactions may
be occurring, each of which may affect the count.
•
COUNT(DISTINCT expr,[expr...])
[970]
Returns a count of the number of rows with different non-
NULL expr
values.
COUNT(DISTINCT)
[970] returns
0
if there were no matching rows.
mysql> SELECT COUNT(DISTINCT results) FROM student;
In MySQL, you can obtain the number of distinct expression combinations that do not contain
NULL
by giving a list of expressions. In standard SQL, you would have to do a concatenation of all
expressions inside
COUNT(DISTINCT ...)
[970] .
•
GROUP_CONCAT(expr)
[970]
This function returns a string result with the concatenated non-
NULL
values from a group. It returns
NULL
if there are no non-
NULL
values. The full syntax is as follows:
GROUP_CONCAT([DISTINCT] expr [, expr ...]
[ORDER BY { unsigned_integer | col_name | expr }
[ASC | DESC] [, col_name ...]]
[SEPARATOR str_val ])
mysql> SELECT student_name,
-> GROUP_CONCAT(test_score)
-> FROM student
-> GROUP BY student_name;
Or:
mysql> SELECT student_name,
-> GROUP_CONCAT(DISTINCT test_score
-> ORDER BY test_score DESC SEPARATOR ' ')
-> FROM student
-> GROUP BY student_name;
In MySQL, you can get the concatenated values of expression combinations. To eliminate duplicate
values, use the
DISTINCT
clause. To sort values in the result, use the
ORDER BY
clause. To sort in