Hi, I'm still working on my movie website. I'm trying to combine the worlds of action movies and quantitative analysis. To do this I have stored a lot of information in my movie content types and have several linked content types such as actors, directors, main characters etc.
What I want to do now is to aggregate this quantitative information by various dimensions. One example would be the average bodycount a certain main character (e.g. the Terminator) has across all of his movies. I want to be able to do this on the fly so that I don't have the update every related article after addig a movie. The movie content type should hold all the information including the links to the other content types and the other content types should get a substantial part of their information from the movie content type.
I have played around with the code pack and the SQL pack. I believe I need to use a Before Render field to pull the information via an SQL query and then store it on the fly in a field for the content item I am rendering. For pulling the average bodycount ("BC") for a main character ("actionheld") whose content view I am loading I believe the SQL query would have to be something like this:
SELECT AVG BC FROM #__cck_store_item WHERE actionheld = 'cck->id'
However, I can't figure out how to run this SQL query in a before render field. I have read the "Using Code Pack"-tutorial, however, I am afraid I need some more guidance as I don't understand the coding enough.
Can anyone point me in the right direction on how to pull data from the SQL-database and calculate averages / sums on the fly?
EDIT: To give you an example of the underlying information, here is a link to the character "Terminator": https://ratingaction.com/filme-und-mehr/actionhelden/terminator As you can see, I've been able to set up a list module that lists all movies where I have selected "Terminator" as main character. Now I would like to be able to calculate averages, sums etc. for the figures like BC, S5+ etc.