PC SOFT

ONLINE HELP
 WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English EN
  • SQL Sum query on the entire data file
  • SQL sum query on a grouping
  • SQL sum query on a grouping by programming
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadApple WatchUniversal Windows 10 AppWindows Mobile
Others
Stored procedures
To perform a sum of values coming from a data file, you have the ability to perform a browse loop and to add up a value in a Numeric variable.
The best method to perform this sum consists in using an SQL query.
Let's see how to perform a sum by using:
SQL Sum query on the entire data file
A Sum query is used to add up a value found in all the records of a data file.
In this example, we are going to perform the sum of all orders found in the ORDER file.
The steps for creating this SQL sum query are as follows:
  1. Click New among the quick access buttons.
    • In the window that is displayed, click "Queries".
    • The query creation wizard starts.
  2. Specify that you want to create a select query ("Select" option).
    Query creation wizard

    Go to the next step of the wizard.
  3. The query description window appears.
  4. Give a name and a caption to the query:
    Name and caption of query
  5. In the left section of the description window, choose the file item for which the sum will be performed (TotalBT in our example).
  6. Double-click the item name to add it into the list of query items:
    Selecting the sum item
  7. In the right section of the editor, click the "Sum" button. The following window is displayed:
    Characteristics of the sum
  8. Validate. The query description window is updated:
    Query description
  9. Validate the query description window. The query is displayed in the editor:
    Displaying the query in the editor
  10. Save the query (Ctrl + S).
  11. Press F2 to see the SQL code:
    SQL code of query
  12. Run the query test (GO in the quick access buttons).
  13. The query can be run in the program by HExecuteQuery.
SQL sum query on a grouping
You have the ability to create a query that performs the sum of a value according to a group criterion.
In this example, we are going to perfom the sum or ordes by delivery mode.
The steps for creating a Sum query on a grouping are as follows:
  1. Create a Sum query (see previous paragraph).
  2. In the data file, select the item that will be used as group argument. In our example, we are going to choose the caption of delivery mode.
  3. Add this item to the query items (double-click the item name for example).
    Group item
  4. Validate the query description window. The query is displayed in the editor:
    Displaying the query in the editor
  5. Save the query (Ctrl + S).
  6. Press F2 to see the SQL code:
    SQL code of query
  7. Run the query test (GO in the quick access buttons).
  8. The query can be run in the program by HExecuteQuery.
SQL sum query on a grouping by programming
The SQL queries can be directly written by programming in the WLanguage code. To do so, you must:
  1. Create a Data source variable to represent the query at runtime.
  2. Create a Character String variable to contain the SQL code of the query and write the SQL code in this variable.
  3. Run the SQL query with HExecuteSQLQuery.
  4. Browse the result with the HReadXXX functions.
Code sample
Src1 is Data Source
sSQLCode is string
 
// Sum or orders by payment mode
sSQLCode = [
SELECT PAYMENT.PaymentCap, SUM(ORDER.TotalBT) AS SumOrders
FROM ORDER, PAYMENT
WHERE ORDER.PaymentID=PAYMENT.PaymentID
GROUP BY PaymentCap
]
 
HExecuteSQLQuery(Src1, hQueryDefault, sSQLCode)
FOR EACH Src1
Trace(Src1.PaymentCap, Src1.SumOrders)
END
See also
Minimum version required
  • Version 9
This page is also available for…
Comments
Report: Editor not working well on version 25
The editor is not working well on any 25s IDE (webdev, mobile or windev).

What happens is that if you create a Query by the wizard (any kind of Query) and then you edit this query by the editor manually, the commands to copy, paste or cut makes a mess in the query, sometimes it doesn´t do anything, sometimes it deletes part of your query, sometimes it paste the content in a different place.

Probably is just a small bug, but definetly does not make the work 10 times faster, it has happened from pre-release to final version of 25.
Angel
09 Jan. 2021