4D v13.4

Using Statistical functions

Home

 
4D v13.4
4D SQL Reference
Tutorial
Using Statistical functions

Using Statistical functions  


 

 

Sometimes it can be useful to get statistical information about certain values. SQL includes many aggregate functions like MIN, MAX, AVG, SUM and so on. Using aggregate functions, we would like to get information about the number of tickets sold each year. The result will be sorted by year.
To do this, we must total all the tickets sold for each movie and then sort the result by year.

  • The initial query in 4D code would be:

     C_LONGINT($vMin;$vMax;$vSum)
     C_REAL($vAverage)
     C_TEXT($AlertTxt)
     
     REDUCE SELECTION([MOVIES];0)
     $vMin:=0
     $vMax:=0
     $vAverage:=0
     $vSum:=0
     ALL RECORDS([MOVIES])
     $vMin:=Min([MOVIES]Sold_Tickets)
     $vMax:=Max([MOVIES]Sold_Tickets)
     $vAverage:=Average([MOVIES]Sold_Tickets)
     $vSum:=Sum([MOVIES]Sold_Tickets)
     $AlertTxt:=""
     $AlertTxt:=$AlertTxt+"Minimum tickets sold: "+String($vMin)+Char(13)
     $AlertTxt:=$AlertTxt+"Maximum tickets sold: "+String($vMax)+Char(13)
     $AlertTxt:=$AlertTxt+"Average tickets sold: "+String($vAverage)+Char(13)
     $AlertTxt:=$AlertTxt+"Total tickets sold: "+String($vSum)+Char(13)
  • Using SQL code, the above query becomes:

     C_LONGINT($vMin;$vMax;$vSum)
     C_REAL($vAverage)
     C_TEXT($AlertTxt)
     
     $vMin:=0
     $vMax:=0
     $vAverage:=0
     $vSum:=0
     Begin SQL
        SELECT MIN(Sold_Tickets),
        MAX(Sold_Tickets),
        AVG(Sold_Tickets),
        SUM(Sold_Tickets)
        FROM MOVIES
        INTO :$vMin, :$vMax, :$vAverage, :$vSum;
     End SQL
     $AlertTxt:=""
     $AlertTxt:=$AlertTxt+"Minimum tickets sold: "+String($vMin)+Char(13)
     $AlertTxt:=$AlertTxt+"Maximum tickets sold: "+String($vMax)+Char(13)
     $AlertTxt:=$AlertTxt+"Average tickets sold: "+String($vAverage)+Char(13)
     $AlertTxt:=$AlertTxt+"Total tickets sold: "+String($vSum)+Char(13)
     ALERT($AlertTxt)
  • Using generic SQL commands, the above query becomes:

     C_LONGINT($vMin;$vMax;$vSum)
     C_REAL($vAverage)
     C_TEXT($tQueryTxt)
     C_TEXT($AlertTxt)
     
     $vMin:=0
     $vMax:=0
     $vAverage:=0
     $vSum:=0
     SQL LOGIN(SQL_INTERNAL;"";"")
     $tQueryTxt:=""
     $tQueryTxt:=$tQueryTxt+"SELECT MIN(Sold_Tickets), MAX(Sold_Tickets), AVG(Sold_Tickets), SUM(Sold_Tickets)"
     $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
     SQL EXECUTE($tQueryTxt;$vMin;$vMax;$vAverage;$vSum)
     SQL LOAD RECORD(SQL All Records)
     SQL LOGOUT
     $AlertTxt:=""
     $AlertTxt:=$AlertTxt+"Minimum tickets sold: "+String($vMin)+Char(13)
     $AlertTxt:=$AlertTxt+"Maximum tickets sold: "+String($vMax)+Char(13)
     $AlertTxt:=$AlertTxt+"Average tickets sold: "+String($vAverage)+Char(13)
     $AlertTxt:=$AlertTxt+"Total tickets sold: "+String($vSum)+Char(13)
     ALERT($AlertTxt)
  • Using the SQL EXECUTE IMMEDIATE command, the query above becomes:

     C_LONGINT($vMin;$vMax;$vSum)
     C_REAL($vAverage)
     C_TEXT($tQueryTxt)
     C_TEXT($AlertTxt)
     
     $vMin:=0
     $vMax:=0
     $vAverage:=0
     $vSum:=0
     $tQueryTxt:=""
     $tQueryTxt:=$tQueryTxt+"SELECT MIN(Sold_Tickets), MAX(Sold_Tickets), AVG(Sold_Tickets), SUM(Sold_Tickets)"
     $tQueryTxt:=$tQueryTxt+" FROM MOVIES"
     $tQueryTxt:=$tQueryTxt+" INTO :$vMin, :$vMax, :$vAverage, :$vSum;"
     Begin SQL
        EXECUTE IMMEDIATE :$tQueryTxt;
     End SQL
     $AlertTxt:=""
     $AlertTxt:=$AlertTxt+"Minimum tickets sold: "+String($vMin)+Char(13)
     $AlertTxt:=$AlertTxt+"Maximum tickets sold: "+String($vMax)+Char(13)
     $AlertTxt:=$AlertTxt+"Average tickets sold: "+String($vAverage)+Char(13)
     $AlertTxt:=$AlertTxt+"Total tickets sold: "+String($vSum)+Char(13)
     ALERT($AlertTxt)

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 Using Aggregate functions button.

 
PROPERTIES 

Product: 4D
Theme: Tutorial