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 the SQL code, the above query becomes:
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
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 the SQL EXECUTE IMMEDIATE command, the query above becomes:
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
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.