CODEWITHSUNDEEP
mysqlsql
faza
faza

Reputation: 257

sum based joining table on comma seperated values

I have table cost like this

enter image description here

and table benang

enter image description here

can I sum (benang.biaya) when id_benang in table cost have same values on table benang

so the result like this

id_benang biaya

8 = 4000000

6 = 4000000

       8000000

sory for my bad english

Upvotes: 0

Views: 58

Answers (1)

Max
Max

Reputation: 59

Presuming you want the total value from the benang table next to the cost information...

SELECT kain, id_cuci, cost_id_benang, SUM(biaya)
FROM
(
    SELECT b.id_benang, biaya, c.kain, c.id_cuci, c.id_benang as cost_id_benang
    FROM BENANG b
    INNER JOIN 
    COST c
    ON LOCATE( RTRIM(CAST(b.id_benang AS CHAR(10))),c.id_benang) > 0
)AS seperatedData
GROUP BY kain, id_cuci, cost_id_benang

Using this sample schema and seed data:

CREATE TABLE COST (kain int, id_benang varchar(10), id_cuci int);
CREATE TABLE BENANG (id_benang int, biaya int);

INSERT INTO COST (kain, id_benang, id_cuci)
VALUES(8,'8,6', 3);

INSERT INTO BENANG(id_benang, biaya)
SELECT 5,3000000 UNION ALL
SELECT 6,4000000 UNION ALL
SELECT 7,3000000 UNION ALL
SELECT 8,4000000;

Upvotes: 1

Related Questions