mysql计数和分组，排序

Question

我有一张这样的 table ..

---

| name    | product 
---------------------
 | Amy     | Doll
 | Allan   | Bat
 | Bob     | Pen 
 | Charlie | Eraser
 | Charlie | Pencil
 | Charlie | Sharpener
 | Allan   | Ruler
 | Anoop   | Candy
 *******************

我想要每个客户购买的产品数量。输出应排序为 1) 按降序购买的产品数量和 2) 购买相同数量产品的客户应按字母顺序排序。

输出:

*******************
 | name     | number 
--------------------
 | Charlie  | 3             
 | Allan    | 2               
 | Amy      | 1
 | Anoop    | 1
 | Bob      | 1              
 *******************

Answer 1

使用GROUP BY:

SELECT  name ,   COUNT(*) AS `number`
FROM your_table
GROUP BY name
ORDER BY `number` DESC, name;

LiveDemo