sql group by sub group

Question

I am trying to work out the majority sub group of a group. For an example let's say my table looks like this:

+--------------------------------------------------+
|   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       |
+--------------------------------------------------+

I've tried to find a pure sql solution so that i can see: in each city, which colour of car has the largest quantity.

I can do:

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

to get:

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

but is there anyway i can use a sub query to get the max of the results which would return the max colour for each city, so the results would show:

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

I can do these calculations in my Python script but would prefer a pure SQL solution.

Hope this makes sense, thanks for any help in advance :)

Tommy

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