I was looking for a way to layer aggregate functions in SQL, and the only way I could find to do it was with sub-selects.
I wanted to group my data by unique values in a column, count the members of each group, then return count for the group with the most members.
I ended up doing a select for the grouping and counting, then nesting that inside the "from" clause of a query that pulled out the max.
To make things just slightly more complicated, I was grouping and counting number of rows per day based on row timestamps, so the sub select has simple date arithmetic in it.
select
max(ct) as max
from
(
select
count(*) as ct,
DATE_FORMAT(visitTime,'%Y-%m-%e') as dt
from
log_userLog
group by
DATE_FORMAT(visitTime,'%Y-%m-%e')
) as tempTable
Dated: 03/17/2006