Using the GROUP BY clause

We would like to get some information about the number of tickets sold each year starting with 1979. The result will be sorted by year.
To do this, we must total all the tickets sold for every movie in each year more recent than 1979, and then sort the result by year.

• The initial query in 4D code would be:
  
  
    ` Using standard 4D code
 ARRAY INTEGER(aMovieYear;0)
 ARRAY LONGINT(aSoldTickets;0)
 C_LONGINT($MovieYear;$vCrtMovieYear;$i)
 
 REDUCE SELECTION([MOVIES];0)
 $MovieYear:=1979
 QUERY([MOVIES];[MOVIES]Year_of_Movie>=$MovieYear)
 ORDER BY([MOVIES];[MOVIES]Year_of_Movie;>)
 $vCrtMovieYear:=0
 $vInd:=Size of array(aMovieYear)
 For($i;1;Records in selection([MOVIES]))
    If([MOVIES]Year_of_Movie#$vCrtMovieYear)
       $vCrtMovieYear:=[MOVIES]Year_of_Movie
       $vInd:=$vInd+1
       INSERT IN ARRAY(aMovieYear;$vInd;1)
       aMovieYear{$vInd}:=$vCrtMovieYear
       INSERT IN ARRAY(aSoldTickets;$vInd;1)
    End if
    aSoldTickets{$vInd}:=aSoldTickets{$vInd}+[MOVIES]Sold_Tickets
    NEXT RECORD([MOVIES])
 End for
  ` Initialize the rest of the list box columns in order to display the information
 ARRAY TEXT(aTitles;Size of array(aMovieYear))
 ARRAY TEXT(aDirectors;Size of array(aMovieYear))
 ARRAY TEXT(aMedias;Size of array(aMovieYear))
 ARRAY LONGINT(aNrActors;Size of array(aMovieYear))

• Using the SQL code, the above query becomes:
  
  
    ` Using 4D SQL
 ARRAY INTEGER(aMovieYear;0)
 ARRAY LONGINT(aSoldTickets;0)
 C_LONGINT($MovieYear)
 
 REDUCE SELECTION([MOVIES];0)
 $MovieYear:=1979
 Begin SQL
    SELECT Year_of_Movie, SUM(Sold_Tickets)
    FROM MOVIES
    WHERE Year_of_Movie >= :$MovieYear
    GROUP BY Year_of_Movie
    ORDER BY 1
    INTO :aMovieYear,  :aSoldTickets;
 End SQL
  ` Initialize the rest of the list box columns in order to display the information
 ARRAY TEXT(aTitles;Size of array(aMovieYear))
 ARRAY TEXT(aDirectors;Size of array(aMovieYear))
 ARRAY TEXT(aMedias;Size of array(aMovieYear))
 ARRAY LONGINT(aNrActors;Size of array(aMovieYear))

• Using generic SQL commands, the above query becomes:
  
  
    ` Using ODBC commands
 C_TEXT($tQueryTxt)
 ARRAY LONGINT(aSoldTickets;0)
 ARRAY INTEGER(aMovieYear;0)
 C_LONGINT($MovieYear)
 
 REDUCE SELECTION([MOVIES];0)
 $MovieYear:=1979
 SQL LOGIN(SQL_INTERNAL;"";"")
 $tQueryTxt:=""
 $tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, SUM(Sold_Tickets)"
 $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
 $tQueryTxt:=$tQueryTxt+" WHERE Year_of_Movie >= :$MovieYear"
 $tQueryTxt:=$tQueryTxt+" GROUP BY Year_of_Movie"
 $tQueryTxt:=$tQueryTxt+" ORDER BY 1"
 SQL EXECUTE($tQueryTxt;aMovieYear;aSoldTickets)
 SQL LOAD RECORD(SQL all records)
 SQL LOGOUT
  ` Initialize the rest of the list box columns in order to display the information
 ARRAY TEXT(aTitles;Size of array(aMovieYear))
 ARRAY TEXT(aDirectors;Size of array(aMovieYear))
 ARRAY TEXT(aMedias;Size of array(aMovieYear))
 ARRAY LONGINT(aNrActors;Size of array(aMovieYear))

• Using the QUERY BY SQL command, the above query becomes:
  
  
    ` Using QUERY BY SQL
 ARRAY LONGINT(aSoldTickets;0)
 ARRAY INTEGER(aMovieYear;0)
 C_LONGINT($MovieYear)
 
 REDUCE SELECTION([MOVIES];0)
 $MovieYear:=1979
 QUERY BY SQL([MOVIES];"Year_of_Movie >= :$MovieYear")
 ORDER BY([MOVIES];[MOVIES]Year_of_Movie;>)
 $vCrtMovieYear:=0
 $vInd:=Size of array(aMovieYear)
 For($i;1;Records in selection([MOVIES]))
    If([MOVIES]Year_of_Movie#$vCrtMovieYear)
       $vCrtMovieYear:=[MOVIES]Year_of_Movie
       $vInd:=$vInd+1
       INSERT IN ARRAY(aMovieYear;$vInd;1)
       aMovieYear{$vInd}:=$vCrtMovieYear
       INSERT IN ARRAY(aSoldTickets;$vInd;1)
    End if
    aSoldTickets{$vInd}:=aSoldTickets{$vInd}+[MOVIES]Sold_Tickets
    NEXT RECORD([MOVIES])
 End for
  ` Initialize the rest of the list box columns in order to display the information
 ARRAY TEXT(aTitles;Size of array(aMovieYear))
 ARRAY TEXT(aDirectors;Size of array(aMovieYear))
 ARRAY TEXT(aMedias;Size of array(aMovieYear))
 ARRAY LONGINT(aNrActors;Size of array(aMovieYear))

• Using the SQL EXECUTE IMMEDIATE command, the query above becomes:
  
  
    ` Using dynamic SQL by EXECUTE IMMEDIATE
 C_TEXT($tQueryTxt)
 ARRAY LONGINT(aSoldTickets;0)
 ARRAY INTEGER(aMovieYear;0)
 C_LONGINT($MovieYear)
 
 $MovieYear:=1979
 $tQueryTxt:=""
 $tQueryTxt:=$tQueryTxt+"SELECT Year_of_Movie, SUM(Sold_Tickets)"
 $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
 $tQueryTxt:=$tQueryTxt+" WHERE Year_of_Movie >= :$MovieYear"
 $tQueryTxt:=$tQueryTxt+" GROUP BY Year_of_Movie"
 $tQueryTxt:=$tQueryTxt+" ORDER BY 1"
 $tQueryTxt:=$tQueryTxt+" INTO :aMovieYear, :aSoldTickets;"
 Begin SQL
    EXECUTE IMMEDIATE :$tQueryTxt;
 End SQL
  ` Initialize the rest of the list box columns in order to display the information
 ARRAY TEXT(aTitles;Size of array(aMovieYear))
 ARRAY TEXT(aDirectors;Size of array(aMovieYear))
 ARRAY TEXT(aMedias;Size of array(aMovieYear))
 ARRAY LONGINT(aNrActors;Size of array(aMovieYear))

To test all the above examples, launch the "4D SQL Code Samples" database and go to the main window. You can then choose the query mode and press the GROUP BY clause button.