Search code examples
mysqlgroup-bycolumnsorting

MySql: Group By with sorting

i've got some trouble in group by

Table VS Wanted Result

//table
ID  | Quantity | Price
----+----------+---------
1902|    2     |  100
1915|    1     |  20
2010|    2     |  30
2052|    3     |  20

//Wanted result
ID  | Quantity | Price
----+----------+---------
1900|    3     |  120
2000|    5     |  50

Help me to solve this. Thank you very much

Solution

  • With making lots of assumptions and purely looking at your current table and desired results, here is something that should work for you (note, change table with your actual table name).

    SELECT group_by_id, 
       Sum(quantity), 
       Sum(price) 
FROM   (SELECT LEFT(id, 2) AS group_by_id, 
               quantity, 
               price 
        FROM   table) AS tdata 
GROUP  BY group_by_id

    Basically, we use a sub-query to get the first 2 characters of each id column and then group by this column in the outer query to calculate our quantity and price.