Purpose
Use the built-in  functions min(), max(), count() and sum() of the SQL database to determine the minimun, maximum, count and sum()  of a group of table rows.

Please note: If you need more than one of these functions in a database call, you need to formulate it as a "join" of two tables, since /guixt/dbselect does not allow us to specify different target fields for simple select calls (one table).

We use the function module /guixt/dbselect and the Open Call Interface.

Example 1
We display a table which shows the highest document number for each sales document type:

 

GuiXT Script

GuiXT 

Table (1,80) (20,127) title="Highest document number per document type" _
    name="vbakstat" fixedColumns=2
 
 Column "Document type" size=16 name="auart"
 Column "Max number" size=30 name="vbeln"

InputScript

GuiXT 
// GuiXT table
 CreateTable V[vbakstat] auart vbeln
 
 // SQL parameters
 set V[tablename] "VBAK"
 set V[fields] "AUART,max( VBELN ) as VBELN"
 set V[groupby] "AUART"
 set V[orderby] "AUART"
 
 // database call
 Call "/GUIXT/DBSELECT" _
   export.table="tablename" _
   export.fields="fields" _
   export.orderBy="orderby" _
   export.groupby="groupby" _
   import.values="vbakstat"

Example 2
We add the document type text and display the lowest number, highest  number and total count of all documents of the given type.

 

GuiXT Script

GuiXT 

Table (1,80) (24,172) _ 
  title="Sales document statsitics" name="vbakstat" fixedColumns=5
 
 Column "Document type" size=16 name="auart" -readOnly
 Column "Text" size=40 name="text" -readOnly
 Column "Min number" size=10 name="min" -readOnly
 Column "Max number" size=10 name="max" -readOnly
 Column "Count" size=10 name="count" -alignRight -readOnly

InputScript

GuiXT 

 // GuiXT table
 CreateTable V[vbakstat] auart text min max count
 
 // SQL parameters
 set V[tablename] "VBAK JOIN TVAKT ON VBAK~AUART = TVAKT~AUART"
 set V[fields] "VBAK~AUART,TVAKT~BEZEI,MIN( VBAK~VBELN ),
                                      MAX( VBAK~VBELN ),COUNT(*) AS I1"
 set V[condition] "TVAKT~SPRAS = '&V[_language]'"
 set V[orderby] "VBAK~AUART"
 set V[groupby] "VBAK~AUART,TVAKT~BEZEI"
 
 // database call
 Call "/GUIXT/DBSELECT" _
   export.table="tablename" _
   export.fields="fields" _
   export.condition="condition" _
   export.orderBy="orderby" _
   export.groupby="groupby" _
   import.values="vbakstat"

Remark
If you need several functions min(), max(), count() in one statement without needing a join, you may force the "join" syntax of /guixt/dbselect with a "dummy" join of the same table. Example:

GuiXT 

// GuiXT table
 CreateTable V[vbakstat] auart min max count
 
 // SQL parameters
 set V[tablename] "VBAK JOIN VBAK AS VBAK2 ON VBAK~VBELN = VBAK2~VBELN"
 set V[fields] "VBAK~AUART,MIN( VBAK~VBELN ),MAX( VBAK~VBELN ),COUNT(*) AS I1"
 set V[orderby] "VBAK~AUART"
 set V[groupby] "VBAK~AUART"
 
 // database call
 Call "/GUIXT/DBSELECT" _
   export.table="tablename" _
   export.fields="fields" _
   export.condition="condition" _
   export.orderBy="orderby" _
   export.groupby="groupby" _
   import.values="vbakstat"

Components InputAssistant