4D v14
Using Statistical functions
- 4D SQL Reference
-
- Tutorial
-
- Introduction
- Receiving an SQL query result in a variable
- Using the WHERE clause
- Receiving an SQL query result into arrays
- Using CAST
- Using the ORDER BY clause
- Using the GROUP BY clause
- Using Statistical functions
- Using the HAVING clause
- Calling 4D methods inside the SQL code
- Joins
- Using Aliases
- Subqueries
- SQL code error tracking and debugging
- Data Definition Language
- External connections
- Connection to the 4D SQL engine via the ODBC Driver
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.
Product: 4D
Theme: Tutorial
4D SQL Reference ( 4D v11 SQL Release 4 )
4D SQL Reference ( 4D v14)
4D SQL Reference ( 4D v12.1)
4D SQL Reference ( 4D v13.4)
4D SQL Reference ( 4D v14 R2)
4D SQL Reference ( 4D v14 R3)
4D SQL Reference ( 4D v14 R4)
