|
|
|
|
| |
The use of COUNT(*) with GROUP BY to count values has a number of advantages over counting occurrences of each distinct column value individually:
:You don't have to know in advance what values are present in the column you're summarizing.
:You need only a single query, not several.
:You get all the results with a single query, so you can sort the output.
The first two advantages are important for expressing queries more easily. The third advantage is important because it affords you flexibility in displaying your results. By default, MySQL uses the columns named in the GROUP BY to sort the results, but you can specify an ORDER BY clause to sort in a different order. For example, if you want number of presidents grouped by state of birth but sorted with the most well-represented states first, you can use an ORDER BY clause as follows:
mysql> SELECT state, COUNT(*) AS count FROM president
-> GROUP BY state ORDER BY count DESC;
| state |
COUNT(*) |
VA
OH
MA
NY
NC
VT
TX
SC
NH
PA
KY
NJ
IA
MO
CA
NE
GA
IL
AR
CT |
8
7
4
4
2
2
2
1
1
1
1
1
1
1
1
1
1
1
1
1 |
When the column you want to sort by is determined by a summary function, you can give the column an alias and refer to the alias in the ORDER BY clause. The preceding query demonstrates this, where the COUNT(*) column is aliased as count. Another way to refer to such a column is by its position in the output. The previous query could have been written as follows instead:
|
|
|
|
|
|
| Link Partners: Asia florist, Flowers to India, Hong kong flowers, Site submit, Cheap web hosting, China florist, Japan florist |
|
