Mysql SUM query is not showing all other column-CodePudding

Hello I'm Trying to Sum the price of the selected topping and show all the names of selected topping, is it possible to do so ?

here is the query

SELECT tord.Kode_topping_order, 
       t.Nama_topping, 
       SUM(t.Harga_topping) AS 'Total Topping' 
FROM topping_order tord 
JOIN topping t ON tord.Kode_Topping = t.Kode_topping 
WHERE tord.Kode_Produk_Order = 39

the output :

However if I Group by Nama_topping the output will be like this:

Is it possible to have All of the Name of topping and all the Total Topping is filled with 12000 ?

CodePudding user response：

Use GROUP BY ... WITH ROLLUP:

SELECT
    COALESCE(tord.Kode_topping_order, 'TOTAL'),
    COALESCE(t.Nama_topping, 'TOTAL'),
    SUM(t.Harga_topping) AS 'Total Topping'
FROM topping_order tord
INNER JOIN topping t
    ON tord.Kode_Topping = t.Kode_topping
WHERE
    tord.Kode_Produk_Order = 39 AND
    (tord.Kode_topping_order IS NULL OR t.Nama_topping IS NOT NULL)
GROUP BY
    tord.Kode_topping_order,
    t.Nama_topping
WITH ROLLUP

CodePudding user response：

You can use SUB Query.

If you can give the both tables data that you have joined I will type the query of course if it is possible to give me the both table data. The sub query will be like this:

SELECT
Kode_topping_order,
(SELECT Nama_topping, SUM(Harga_topping) AS Total_Topping FROM topping) 
FROM topping_order tord 
JOIN topping t ON tord.Kode_Topping = t.Kode_topping 
WHERE tord.Kode_Produk_Order = 39