MySql: Group By with sorting

Question

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

score 2 · Accepted Answer · answered Mar 20 '17 at 04:10

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.

score 0 · Answer 2 · edited Apr 24 '17 at 07:30

You should have one table to store their relationship or have one field for store foreign key.

Example:

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

MySQL

SELECT ParentID,SUM(Quantity) AS TotalQuantity,SUM(Price) AS TotalPrice FROM TableName GROUP BY ParentID

MySql: Group By with sorting

2 Answers2