PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Home | Sign in | English US
  • SQL count query on the entire data file
  • SQL count query on a grouping
  • SQL count 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 count records in a data file, you have the ability to perform a browse loop and to increment an Integer variable.
The best method to perform this count consists in using an SQL query.
Let's see how to perform a count by using:
SQL count query on the entire data file
This count query is used to count all the records found in a data file. The images present the count of all records found in CUSTOMER file.
The different steps for creating this SQL count query are as follows:
  1. Select "File .. New .. Query". The wizard for query creation starts.Click New among the quick access buttons.
    • Click "Queries" in the wheel that is displayed. In the window that is displayed, click "Queries".
    • The wizard for query creation starts.
  2. Select the "Select" option and validate.Specify that you want to create a select query ("Select" option).
    Wizard for query creation

    Go to the next wizard step.
  3. The description window of query is displayed.
  4. Give a name and a caption to the query:
    Name and caption of query
  5. Choose an item (identifier for example) in the data file where the count will be performed.
  6. Add this item to the list of query items.
    Adding the count item
  7. In the right section of the editor, click the "Count" button. In the menu that is displayed, select "Count on the selected item":
    Count menu
    The following window is displayed:
    Describing the count
  8. Validate. The description window of query is upated:
    Query description
  9. Validate the description window of query. The query is displayed in the editor:
    Displaying the query in the editor
  10. Save the query (CTRL S).
  11. Press the F2 key 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 count query on a grouping
You have the ability to create a query that counts records according to a group criterion.
In this example, we are going to count the number of customers per state.
The steps for creating a Count query on a grouping are as follows:
  1. Create a Count 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 zip code.
  3. Add this item to the query items (double-click the item name for example).
    Group item
  4. We are going to add a new action to perform a grouping on the state only. In the right section of the description window, click "Year, state" and select "State".
    State
    The following window is displayed:
    Displaying the state
  5. Validate. The description window of query is upated:
    Query description
  6. Validate the description window of query. The query is displayed in the editor:
    Displaying the query in the editor
  7. Save the query (CTRL S).
  8. Press the F2 key to see the SQL code:
    SQL code of query
  9. Run the query test (GO in the quick access buttons).
  10. The query can be run in the program by HExecuteQuery.
SQL count query on a grouping by programming
The SQL queries can be directly written by programming in the code. To do so, you must:
  1. Create a Data Source variable to represent the query at run time
  2. Create a Character String variable to contain the SQL code 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

// Counts the number of orders by payment type
sSQLCode = [
SELECT PAYMENT.PaymentCap, COUNT(ORDER.OrderID) AS NumberOrders
FROM ORDER, PAYMENT
WHERE ORDER.PaymentID=PAYMENT.PaymentID
GROUP BY PaymentCap
]

HExecuteSQLQuery(Src1, hQueryDefault, sSQLCode)
FOR EACH Src1
Trace(Src1.PaymentCap, Src1.NumberOrders)
END
Minimum required version
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment