query field - substitution number groupjive

Hello

I use a sql request in query field
SELECT `bestscore` FROM `score` WHERE `numplayer` = '[user_id]'

The table score is in a external database. Each player have many bestscore (1 by theme)

I have many questions :

1) Can I use a calculate field in query field as :
SELECT AVG(bestscore) AS avgscore FROM 'score'

2) Can i calculate the avgscore only for the group's member of groupjive ? (with a substitution ?)

1) Can I use a calculate field in query field as :
SELECT AVG(bestscore) AS avgscore FROM 'score'

You can use any valid SQL functions and usages in it. In short, yes.

2) Can i calculate the avgscore only for the group's member of groupjive ? (with a substitution ?)

If you have a substitution you can substitute in to determine that they're in a GJ group then yes. Otherwise, no as your query is on an external table you can't do a JOIN on the GJ table.

Thanks for your response

Before begin i prefere ask you :

If
1)I create a field "bestscore" for each theme with community builder
2)I modify QCM to write the best score in this field (so in cb database)

I can calculate and display the average of bestscore for the group's member
(ex: user1 and user2 are member of the Groupjive "Student of NY". Bestscore user1 = 70%. Bestscore user2 = 80%. I can calculate and display in the groupjive "Student of NY" Average of member = 75%)

Thanks

I've no idea how your score database is structure, but I guess so. If you want to query the score and GJ at the same time they need to be both in the same database. So you'd need to grab the score from your external database and push it to CBs database. The only way to do this is a CB Query Field to grab the score then store it using a Field action or use a Code action with Method set to PHP and do your queries as needed.

Thanks for your response

I modify my database. All tables are in the same database. I added the table score(id, numtheme, bestscore)

So i can calculate the avg score of player's group2 with this request
SELECT AVG(bestscore) AS avgscore
FROM score, groupjive_users
WHERE score.id=groupjive_users.user_id
AND groupjive_users.group=2

Now, I would like calculate the avg score for each group of my site and display it automatically on the presentation's page of each group.

I Have 2 questions
1) How can I place a query field with this request in the presentation's page of each group ?

2) How can I recover the group's number of the presentation's page ? I would like the equivalent of substitution [user_id] but for group's number

I hope that my question is clear. My english is very bad.

1) How can I place a query field with this request in the presentation's page of each group ?

Modify GJs template files and add your query and the output of it directly to the template files. GJ can't render fields. Some locations it parses substitutions, but for general locations it does not. You can find GJs template files at the below location.

components/com_comprofiler/plugin/user/plug_cbgroupjive/templates/

2) How can I recover the group's number of the presentation's page ? I would like the equivalent of substitution [user_id] but for group's number

I've no idea what the presentation page is, but there is no GJ substitutions so you can't. You can write actual PHP code to handle this behavior in GJ.