Mysql – How to sum of column by depending other column in the sql

jsonMySQLmysql-5.7sum

how to calculate / SUM of column by the latest data of updateid column ?

I try this :

SELECT * FROM yourTable;

enter image description here

SELECT 
ANY_VALUE(code) AS code,
COUNT(*) AS total_rows,
SUM(data->'$.chart."21"') AS total_a
FROM yourTable
GROUP BY code
ORDER BY code;

from the query abore, it sums all in rows

From above query it sums all rows, it should be sum the latest update id

anyone can solve this problem ?

here is the fiddle : https://www.db-fiddle.com/f/qHnaHNQtEhDasLiSB4Jhv5/0

Best Answer

SELECT 
t1.code,
COUNT(*) AS total_rows,
SUM(t1.data->'$.chart."21"') AS total_a
FROM yourTable AS t1
JOIN (SELECT code, MAX(updateid) AS updateid
      FROM yourTable
      GROUP BY code) AS t2 ON t1.code = t2.code
                          AND t1.updateid = t2.updateid
GROUP BY t1.code
ORDER BY t1.code;
Related Question