CODEWITHSUNDEEP
mysqljoinsum
Led
Led

Reputation: 517

MYSQL sum based on another table

assuming i have this two tables,

table A

+----------+---------------------------------+
| product  | categories                      |
+----------+---------------------------------+
| vegetable| carrots, cabbage, string beans  |
+----------+---------------------------------+
| fruit    | apple, oranges                  |
+----------+---------------------------------+

table B

+--------------+----+
|category      |sale|
+--------------+----+
| carrots      | 10 |
+--------------+----+
| cabbage      | 5  |
+--------------+----+
| apple        | 11 |
+--------------+----+
| string beans | 5  |
+--------------+----+
| oranges      | 7  |
+--------------+----+

my goal is to get the total amount of sale per product.

+----------+----+
| product  |sale|
+----------+----+
| vegetable| 20 |
+----------+----+
| fruit    | 18 |
+----------+----+

i thought it was similar to this but i think its applies differently to my question,

Upvotes: 0

Views: 140

Answers (2)

Sagar Gangwal
Sagar Gangwal

Reputation: 7980

SELECT A.product,SUM(B.SALE) FROM TABLEA A
INNER JOIN TABLEB B ON FIND_IN_SET(B.category, REPLACE(A.categories, ', ', ',')) > 0
GROUP BY A.product

OR

Assuming categories always containing single word category.

 SELECT A.product,SUM(B.SALE) FROM TABLEA A
    INNER JOIN TABLEB B ON FIND_IN_SET(B.category, REPLACE(A.categories, ' ', '')) > 0
    GROUP BY A.product

Try above code.

Hope this will helps.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522732

As has already been mentioned, you should probably avoid storing your inventory as CSV data in the table. If you must proceed, you can try joining using MySQL's FIND_IN_SET() function. This function can search a CSV string for a particular term, and return the index, if found, otherwise zero.

SELECT
    a.product,
    SUM(b.sale) AS sale
FROM tableA a
INNER JOIN tableB b
    ON FIND_IN_SET(b.category, REPLACE(a.categories, ', ', ',')) > 0
GROUP BY a.product
ORDER BY SUM(b.sale) DESC;

Notes:

FIND_IN_SET() doesn't take any whitespace you might have in the CSV string into account. This must also be matched. Hence, I removed the whitespace in your CSV string before matching to it. This can get tricky when you also have terms like string beans which themselves have whitespace, because we can't do a blanket replacement.

Output:

enter image description here

Demo here:

Rextester

Upvotes: 6

Related Questions