Querying a machform

[i_abidi]

Hey Community,

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 !

[posipova]

Are you using ThingWorx for this? If so, how are you integrating it?

[i_abidi]

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.

[MichalC]

1. Are z1,z2 & z3 column headers in a table?

2. You're missing GROUP BY in your query

[i_abidi · Accepted solution]

z1 z2 and z3 are fields names in my machform (equivalent of a table if i was querying a MySQL database instead of machform database) . But i found the solution to that i was missing .'option' i needed z1;'option' instead of z1