CODEWITHSUNDEEP
mysqlsqlgroup-by
kheraud
kheraud

Reputation: 5288

Order by on a group sum criteria

Is it possible to order return rows with a criteria on a sum group ?

For example, my data are :

Id     Price     Product     Category
1      12        Book1       Car
2      1         Book2       Art
3      8         Book3       Car
4      7         Book4       Art
5      11        Book5       Car
6      24        Book6       Bridge

As the sum of Car books is 31, the sum of Art books is 8 and the sum of Bridge books is 24, I would like to have the following result (Car first, then Bridge and then Art):

Id     Price     Product     Category
1      12        Book1       Car
3      8         Book3       Car
5      11        Book5       Car
6      24        Book6       Bridge
2      1         Book2       Art
4      7         Book4       Art

On the other hand, I would like to add other Order by criteria (in the example, "Product" criteria). I have tried many things using ORDER BY and GROUP BY but it always aggregate my results.

Thanks for help !

Upvotes: 0

Views: 382

Answers (1)

Matt Fenwick
Matt Fenwick

Reputation: 49095

You could do something like this:

select 
  l.* from table l
inner join (
  select category, sum(price) as total from table group by category
) r
on l.category = r.category
order by r.total, <some_other_column>

This is the procedure I followed:

  1. find the subtotals
  2. join the original table to the subtotals
  3. order the original table by those subtotals

Upvotes: 6

Related Questions