Database Reference
In-Depth Information
+--------+----------+
| N | 8 |
+--------+----------+
But eight state names also begin with N . To find all most-frequent values when there
may be more than one, use a user-defined variable or subquery to determine the max‐
imum count, then select those values with a count equal to the maximum:
mysql> SET @max = (SELECT COUNT(*) FROM states
-> GROUP BY LEFT(name,1) ORDER BY COUNT(*) DESC LIMIT 1);
mysql> SELECT LEFT(name,1) AS letter, COUNT(*) FROM states
-> GROUP BY letter HAVING COUNT(*) = @max;
+--------+----------+
| letter | COUNT(*) |
+--------+----------+
| M | 8 |
| N | 8 |
+--------+----------+
mysql> SELECT LEFT(name,1) AS letter, COUNT(*) FROM states
-> GROUP BY letter HAVING COUNT(*) =
-> (SELECT COUNT(*) FROM states
-> GROUP BY LEFT(name,1) ORDER BY COUNT(*) DESC LIMIT 1);
+--------+----------+
| letter | COUNT(*) |
+--------+----------+
| M | 8 |
| N | 8 |
+--------+----------+
8.12. Date-Based Summaries
Problem
You want to produce a summary based on date or time values.
Solution
Use GROUP BY to place temporal values into categories of the appropriate duration. Often
this involves using expressions that extract the significant parts of dates or times.
Discussion
To sort rows temporally, use ORDER BY with a temporal column. To summarize rows
instead, based on groupings into time intervals, determine how to categorize rows into
the proper intervals and use GROUP BY to group them accordingly.
Search WWH ::




Custom Search