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

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 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

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 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 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