1

I have problem sum of the amount from table books.

+----+-----------+-----------+----------------+
| id | id_book   | title     | amount         |
+----+-----------+-----------+----------------+
|  1 | 1         |Book 1     |[[1],[1]]       |
+----+-----------+-----------+----------------+

i want to sum column amount, anyone can help me? thx before.

Improve this question
12
  • 1
    Side note: if you can normalize your table structures. otherwise you will have to write very tricky SQL to parse out that JSON array Commented Jul 26, 2022 at 9:30
  • Parse (JSON_TABLE) then sum. Commented Jul 26, 2022 at 9:30
  • can u write the code? Commented Jul 26, 2022 at 9:33
  • What mysql version? Commented Jul 26, 2022 at 9:39
  • i already wrote some SQL code before on stackoverflow to parse out JSON but it is not pretty and rather complex-> db-fiddle.com/f/9tcgRBJJcGrEsSPEAfmPzq/7 nest the complete SQL into a JOIN statement ( INNER JOIN (SELECT ,,,,) ON ... ) and join on id to get the data i assumming id to be a auto_increment in your table.. also note mine example supports up to 100 array elements if you need more you need to add a other ( SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) Commented Jul 26, 2022 at 9:45
Related questions
0
MySQL/PHP SUM data in an ARRAY
0
sum of values in an array
0
mysql, How to sum a value in an array which stored as a field value?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.