SQL Select calculation

General
1

Im trying to select a calculation out of my database.

[{"id":"8db08aab.865c88","type":"mysql","z":"1f216cc0.281a93","mydb":"23c1ad96.82d712","name":"","x":550,"y":1400,"wires":[["8f679bb2.aa3e18"]]},{"id":"34d9ace1.488fa4","type":"function","z":"1f216cc0.281a93","name":"Select statement","func":"msg.topic = \"select concat(round(sum(case when data_soort='AAN' then data_waarde else null end)*100.0/sum(case when data_soort in ('AAN','UIT') then data_waarde else null end),2)) from IOT_DATA\";\nreturn msg;","outputs":1,"noerr":0,"x":310,"y":1400,"wires":[["8db08aab.865c88"]]},{"id":"a47787ef.dd4958","type":"ui_gauge","z":"1f216cc0.281a93","name":"","group":"ef52ec43.10754","order":9,"width":0,"height":0,"gtype":"gage","title":"gauge","label":"units","format":"{{value}}","min":0,"max":"100","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":970,"y":1380,"wires":[]},{"id":"4f278390.0987fc","type":"debug","z":"1f216cc0.281a93","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1010,"y":1440,"wires":[]},{"id":"d9c0977f.85dd48","type":"inject","z":"1f216cc0.281a93","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":1400,"wires":[["34d9ace1.488fa4"]]},{"id":"8f679bb2.aa3e18","type":"function","z":"1f216cc0.281a93","name":"Select output","func":"if (msg.payload >0){\nreturn msg.topic;\n}\nreturn msg;","outputs":1,"noerr":0,"x":730,"y":1400,"wires":[["a47787ef.dd4958","4f278390.0987fc"]]},{"id":"23c1ad96.82d712","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"VEKO_IOT","tz":""},{"id":"ef52ec43.10754","type":"ui_group","z":"","name":"Pers 77","tab":"6b96da3f.ce1c04","order":1,"disp":true,"width":"24","collapse":false},{"id":"6b96da3f.ce1c04","type":"ui_tab","z":"","name":"Machine data","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

But it returns the full name with value. How can i get just the value?

2

Show us what you get from the sql node (using a debug node) so we can more clearly understand the problem.

3 
concat(round(sum(case when data_soort='AAN' then data_waarde else null end)*100.0/sum(case when data_soort in ('AAN','UIT') then data_waarde else null end),2)): "98.50"

This the object i receive, but it should just "98.50"

g

4

If you use

select concat(....) as 'value' from IOT_DATA

then the array element should be
{value: 98.5}
so you can access it by
msg.payload[0].value
The reason it returns an object is that if you said select a,b,c from... then you would need some way of knowing which was which. Because you did not provide an AS clause it has named it with the query string.