I m trying to query a machform and i want to show the average of 3 choices in the machform matrix in output but it didnt seem to work:
SELECT date_created as Date, Line.`option` as Line, z1.`option`as S1, z2.`option` as S2, z3.`option` as S34 , AVG(z1, z2, z3) as SG
FROM ap_form_122320 F
LEFT JOIN ap_element_options Line ON Line.form_id = 122320 AND Line.element_id = 3 AND Line.option_id = F.element_3
LEFT JOIN ap_element_options z1 ON z1.form_id = 122320 AND z1.element_id = 48 AND z1.option_id = F.element_48
LEFT JOIN ap_element_options z2 ON z2.form_id = 122320 AND z2.element_id = 49 AND z2.option_id = F.element_49
LEFT JOIN ap_element_options z3 ON z3.form_id = 122320 AND z3.element_id = 50 AND z3.option_id = F.element_50
Where F.date_created >= [[StartDate]]
and F.date_created <= [[EndDate]]
but i keep getting execution errors. How can I get the average of more than One column/ matrix option in SQL ? thanks a lot !
Solved! Go to Solution.
Yes I m using Thingworx for this because i need the data from my machform to be shown in the mashups i'm designing. I am using it through a thing with machform_connection as base thng template. It is possible but the SQL queries can be challenging to write.