Door 10 Minimum and Maximum

Welcome to Door 10. Today, I would like to talk to you about the FM.SQL.Max and FM.SQL.Min functions. As their names suggest, these two functions determine the lowest or highest value within a result set. As we did yesterday, we first send an SQL query to the database using the FM.SQL.Execute function. We can then apply FM.SQL.Max and FM.SQL.Min. In our example, we want to determine the values of the highest and lowest generated sales. Our script then looks like this:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Generated_Sales FROM Movie" ) ]
Set Variable [ $max ; Value: MBS("FM.SQL.Max"; $SQLRes; 0; ""; "") ]
Set Variable [ $min ; Value: MBS("FM.SQL.Min"; $SQLRes; 0; ""; "") ]
Show Custom Dialog [ "Minimum and Maximum generated …" ;
   "Min: " & $min & "¶Max: " & $max ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

Here, we could again select the range in our rows in the parameters that we want to use for value determination. In our case, it's all of them, so we leave both parameters blank. If we run it like this, we get this dialog:

Min remains blank. Why is that? If we look again at the information in our database, we see that we have rows in which no value is entered, but instead a dash. To get around this and still get a value, we need to add a WHERE clause to filter out this value. In FileMaker, we have selected the Number type for the field, which means that the dash is not accepted as a number and the field is accepted as empty. We can now filter out these fields by writing WHERE Generated_Sales!=0. Then we get the matching number as a minimum.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Generated_Sales FROM Movie WHERE Generated_Sales!=0" ) ]

SQL also has corresponding aggregate functions for minimum and maximum that work directly on the database and not on the result set like the MBS functions. Here they are called MIN and MAX. The same form applies here as we saw yesterday, too.

SELECT Max(Generated_Sales) FROM Movie

First comes the SELECT, then the word MIN or MAX, depending on whether you want the minimum or maximum. Then, in brackets, comes the field from which we want to determine the minimum or maximum. In our case, this is Generated_Sales. Finally, the FROM part follows, and optionally our WHERE part again. With SQL aggregate functions, we don't need to filter out the empty fields. These are simply ignored.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Max(Generated_Sales) FROM Movie" ) ]
Set Variable [ $max ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]
Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Min(Generated_Sales) FROM Movie" ) ]
Set Variable [ $min ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ] 

Show Custom Dialog [ "Minimum and Maximum generated …" ; "Min: " & $min & "¶Max: " & $max ]

Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

I would like to introduce you to another clause in SQL: GROUP BY

GROUP BY combines records into groups that have the same value in one or more fields. Within each of these groups, you can use aggregate functions such as MIN, MAX, SUM, AVG, or COUNT to calculate group results. Without GROUP BY, aggregate functions always refer to the entire table. With GROUP BY, they refer to each group separately.

Let's do this for the years and output the highest Generated_Sales for each year.

SELECT Launch, MAX(Generated_Sales) FROM Movie GROUP BY Launch

In this return, we want to see the Launch field and also the maximum amount. To do this, we first specify the Launch field in the SELECT part, separate it with a comma from our aggregate function MAX, which is then written as we have already discussed. Then comes the FROM part, followed by the GROUP BY clause, after which we specify the field by which we want to group. In our case, these are the years of launch and thus the Launch field.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Launch, MAX(Generated_Sales) FROM Movie GROUP BY Launch" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]

Show Custom Dialog [ "Max generated sales from the i…" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

Now it would also be nice to see the movies that are involved. So just add the Name field to the SELECT part? No, unfortunately it's not that simple. When creating a query with GROUP BY in SQL, you have to pay attention to an important basic rule: All fields in the SELECT part must either be in the GROUP BY clause or be summarized by an aggregate function such as MIN, MAX, SUM, or AVG. If this rule is broken, the query is logically unclear and SQL will abort with an error. Name is not grouped and not aggregated. SQL would therefore not know which movie title to use. That's why we have to support SQL and formulate the query differently.

SELECT  Launch, Name, Generated_Sales
FROM Movie m1
WHERE Generated_Sales = (SELECT MAX(Generated_Sales)
			 FROM Movie m2 
			 WHERE m2.Launch = m1.Launch)

First, in the SELECT part, we again specify the fields we want to get back. Then, in the FROM part, we specify that the information should come from the Movie table. But there is a special feature here, because we specify an alias for the table after the table name. This will be important again in a moment. Then we have a WHERE part in which we make an inner query. To determine the highest annual revenue, the inner query executes a MAX function on the same table, this time under the alias m2. This subquery filters the table so that only movies whose launch year matches the current movie from the outer query are considered. The maximum sales value is then calculated for this group of films. The result of the subquery is thus the highest sales achieved in the respective year. The WHERE condition compares this maximum value from the inner query with the sales of the current film in the outer query. If both values match, it means that this film is one of the highest-grossing films of its release year. Accordingly, this film – or, in the event of a tie, several films from the same year – is included in the overall result. This provides a list of the most successful films per year, each with the year, title, and box office , without the need for a GROUP BY.

In our result, the years are still in no particular order. We want to change this and use the keyword ORDER BY to sort the result. We place this at the end of our outer query and pass the field name Launch, according to which we want to sort the result.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT  Launch, Name, Generated_Sales 
   FROM Movie m1 
   WHERE Generated_Sales = (SELECT MAX(Generated_Sales)
                            FROM Movie m2 
                            WHERE m2.Launch = m1.Launch ) 
                            ORDER BY Launch") ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]
Show Custom Dialog [ "Max generated sales from the i…" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

This gives us a sorted result

That's it for today's door. I hope you enjoy trying it out and I hope to see you again tomorrow.