CODEWITHSUNDEEP
mysql
Arnas Pečelis
Arnas Pečelis

Reputation: 1008

Mysql order by percent of two values sum

So I need to order by percent counted from two values of one field where data is stored in JSON.

The field name where values are stored named program_invested_details and for example value is:

{"invested":"120.00","received":"1.08"}

I need that $query would be (received * 100 / invested) from that field

SELECT *, ($query) AS PERRCENT_TOTAL 
FROM programs_list 
WHERE program_add_status = 4 AND program_status = 1 ORDER BY PERRCENT_TOTAL DESC

how does it possible to make?

Upvotes: 0

Views: 69

Answers (1)

lsowen
lsowen

Reputation: 3828

By default MySQL does not have any ability to parse a JSON string.

One option would be to use an extension such as common_schema which would add the ability to parse JSON and extract fields (see get_option). I am not sure of the performance hit you would take with this extension.

Another option would be to query all the data and parse the JSON in your client program. Once again, there would be significant performance impact if there is a lot of data.

Upvotes: 1

Related Questions