Door 9 Totals and averages

Today, I would like to introduce two functions that you can use to evaluate the data in your results even further: FM.SQL.Sum and FM.SQL.Avg. The FM.SQL.Sum function calculates the total from a column in our results. Let's try this out right away and calculate the total number of screenings for all films. To do this, we have the following code:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Generated_Sales FROM Movie" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Sum"; $SQLRes; 0; ""; "") ]
Show Custom Dialog [ "Sum of generated sales" ; $r ]

First, we'll run our SQL query again. I've decided to limit the output to the column here, but if you need additional columns for the result, that's no problem at all, because we'll define the relevant column later in the FM.SQL.Sum function. This gives us our reference from the FM.SQL.Execute function again, which we can then use as the first parameter in the FM.SQL.Sum function. Furthermore, we specify in the parameters on which column the operation should be applied. Since we only have one column here and the index starts at 0, we specify 0 here. We can also select the range of rows that we want to use from the result for this action. So if you only want to get the box office takings from the first three films in the database as a sum, you can specify 0 and 2 in the parameters. However, since we want all the films here, we simply leave the two parameters blank or we can even leave them out completely. Our result is then displayed in a dialog box.

The same applies to the FM.SQL.Avg function, except that here it is not the sum but the average that is calculated. So if we want to calculate the average movie box office, we can write the code as follow:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Generated_Sales FROM Movie" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Avg"; $SQLRes; 0; ""; "") ]
Show Custom Dialog [ "Average of generated sales" ; $r ]

As we saw with door 5, we are working with the calculated result here. Once again, we have the option of using aggregate functions in SQL to control the whole process. Here, we have the keywords SUM and AVG. The aggregate functions are again applied directly to the database and not to the result first. Here, too, we again use the keyword in the SELECT part. After SELECT, we specify the keyword for our desired function, in this case SUM, followed by the field from which we want to calculate the sum in brackets. Similar to COUNT, we can again use a WHERE clause to preselect the values on which the sum is to be calculated. In our case, we want the sum of the box office takings for all Chris Columbus films.

SELECT SUM(Generated_Sales) 
FROM Movie 
WHERE Director='Chris Columbus'"  

This is what the script looks like:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT SUM(Generated_Sales) FROM Movie WHERE Director='Chris Columbus'" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]
Show Custom Dialog [ "Sum of generated sales generat…" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

It works similarly with AVG.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT AVG(Generated_Sales) FROM Movie WHERE Director='Chris Columbus'" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]
Show Custom Dialog [ "Average of generated sales gen…" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

That brings us to the end of today's door. I hope you enjoyed it.