Database Reference
In-Depth Information
■
Every column listed in
GROUP BY
must be a retrieved column or a valid
expression (but not an aggregate function). If an expression is used in
the
SELECT
, that same expression must be specified in
GROUP BY
. Aliases
cannot be used.
■
Aside from the aggregate calculations statements, every column in your
SELECT
statement should be present in the
GROUP BY
clause.
■
If the grouping column contains a row with a
NULL
value,
NULL
will be
returned as a group. If there are multiple rows with
NULL
values, they'll
all be grouped together.
■
The
GROUP BY
clause must come after any
WHERE
clause and before any
ORDER BY
clause.
Tip
Using
ROLLUP
To obtain values at each group and at a summary level (for each group),
use the
WITH ROLLUP
keyword, as seen here:
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;
In addition to being able to group data using
GROUP BY
, MariaDB also allows
you to filter which groups to include and which to exclude. For example, you
might want a list of all customers who have made at least two orders. To obtain
this data you must filter based on the complete group, not on individual rows.
You've already seen the
WHERE
clause in action (introduced back in Chapter 6,
“Filtering Data.”) But
WHERE
does not work here because
WHERE
filters specific
rows, not groups. As a matter of fact,
WHERE
has no idea what a group is.
So what do you use instead of
WHERE
? MariaDB provides yet another clause
for this purpose: the
HAVING
clause.
HAVING
is similar to
WHERE
. In fact, all types
of
WHERE
clauses you learned about thus far can also be used with
HAVING
. The
only difference is that
WHERE
filters rows and
HAVING
filters groups.
Tip
HAVING
Supports All of
WHERE
'
s
Operators In Chapter 6 and Chapter 7,
“Advanced Data Filtering,” you learned about
WHERE
clause conditions (including wild-
card conditions and clauses with multiple operators). All the techniques and options
you learned about
WHERE
can be applied to
HAVING
. The syntax is identical; just the
keyword is different.
