按子组分组的sql

Question

我正在尝试找出一个组的大多数子组。例如，假设我的表如下所示:

+--------------------------------------------------+
|   city      |  car_colour |  car_type |  qty   |
+--------------------------------------------------+
| ------------------------------------------------ |
| manchester  |  Red        |  Sports   |  7       |
| manchester  |  Red        |  4x4      |  9       |
| manchester  |  Blue       |  4x4      |  8       |
| london      |  Red        |  Sports   |  2       |
| london      |  Blue       |  4x4      |  3       |
| leeds       |  Red        |  Sports   |  5       |
| leeds       |  Blue       |  Sports   |  6       |
| leeds       |  Blue       |  4X4      |  1       |
+--------------------------------------------------+

我试图找到一个纯sql解决方案，以便我可以看到:在每个城市中，哪种颜色的汽车数量最多。

我可以:

select city, cars, sum(qty)
from table
group by city, cars

要得到:

+------------+------+----+
| manchester | red  | 16 |
| manchester | blue |  8 |
| london     | red  |  2 |
| london     | blue |  3 |
| leeds      | red  |  5 |
| leeds      | blue |  7 |
+------------+------+----+

但是无论如何我可以使用子查询来获取结果的最大值，这将返回每个城市的最大颜色，因此结果将显示:

+------------+------+
| manchester | red  |
| london     | blue |
| leeds      | blue |
+------------+------+

我可以在我的 Python 脚本中进行这些计算，但更喜欢纯 SQL 解决方案。

希望这是有道理的，提前感谢您的任何帮助:)

汤米

Answer 1

select distinct p.city, p.car_colour,sq.qty as qty
from         ( select t.car_colour,t.city, sum(t.qty) as qty
                from table1 t
                group by t.car_colour,t.city
             )p
join         ( select q.city,max(q.qty) qty from
                  ( select t.car_colour,t.city, sum(t.qty) as qty
                   from table1 t
                   group by t.car_colour,t.city
                  )q
                 group by q.city
              )sq
on p.city=sq.city and p.qty=sq.qty 

SQL FIDDLE DEMO