cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

We are troubleshooting an issue impacting community login, and it may be intermittently unavailable. Sorry for any inconvenience.

Querying a machform

i_abidi
7-Bedrock

Querying a machform

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 !

ACCEPTED SOLUTION

Accepted Solutions
i_abidi
7-Bedrock
(To:MichalC)

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

View solution in original post

4 REPLIES 4
posipova
20-Turquoise
(To:i_abidi)

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

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
6-Contributor
(To:i_abidi)

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

2. You're missing GROUP BY in your query

 

i_abidi
7-Bedrock
(To:MichalC)

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

Announcements


Top Tags