how to do this calculation in sql query. i have to combine the same product

Here is my query

SELECT
`product_id`,
`product_quantity`,
`product_price`,
`product_price` * `product_quantity` AS `product_total`,
`purch_price`,
`purch_price` * `product_quantity` AS `purch_total`
FROM
`master_stock`
WHERE `product_id`='SSK-SQ2001-20W-6500K'
GROUP BY
`id`
ORDER BY
`id`

Here is the table

product_id product_quantity product_price product_total purch_price purch_total

SQ2001   100       167      1670     134     13400

SQ2001   100       167      1670     135     13500

Now i want to get the result like this (Because it has same id but purchase price is different )

product_id          product_quantity  product_total  purch_total

SQ2001           200        334000      26900

What i try but could not able to get result

SELECT
ms.id,
ms.product_id,
ms.product_name,
sum(ms.product_quantity) AS total_quantity,
ms.product_price*ms.product_quantity AS product_total,
ms.purch_price*ms.product_quantity AS purch_total
FROM
master_stock ms
GROUP BY
ms.product_id
ORDER BY
ms.product_id;

here is my result

Please help Thanks in advance

Answer

If i understand you problem correctly you need to sum up the quantity before multiplying it with the prices. Otherwise you just multiply it with one of the quantities. Here would be the solution to that problem:

SELECT
    ms.id,
    ms.product_id,
    ms.product_name,
    sum(ms.product_quantity) AS total_quantity,
    sum(ms.product_price*ms.product_quantity) AS product_total,
    sum(ms.purch_price*ms.product_quantity) AS purch_total
FROM
    master_stock ms
GROUP BY
    ms.product_id
ORDER BY
    ms.product_id;

fiddle: http://sqlfiddle.com/#!9/6f16ce/49/0