- SQL count query on the entire data file
- SQL count query on a grouping
- SQL count query on a grouping through programming
How to create an SQL query to count?
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: - Click
 in the quick access buttons. - In the window that is displayed, click "Queries".
- The query creation wizard starts.
- Specify that you want to create a select query ("Select" option).
Go to the next step of the wizard. - The query description window appears.
- Give a name and a caption to the query:
- Choose an item (identifier for example) in the data file where the count will be performed.
- Add this item to the list of query items.
- In the right section of the editor, click the "Count" button. In the menu that is displayed, select "Count on the selected item": The following window is displayed:
- Validate. The query description window is updated:
- Validate the query description window. The query is displayed in the editor:
- Save the query (Ctrl + S).
- Press F2 to see the SQL code:
- Run the query test (GO in the quick access buttons).
- 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: - Create a Count query (see previous paragraph)
- 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.
- Add this item to the query items (double-click the item name for example).
- 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". The following window is displayed:
- Validate. The query description window is updated:
- Validate the query description window. The query is displayed in the editor:
- Save the query (Ctrl + S).
- Press F2 to see the SQL code:
- Run the query test (GO in the quick access buttons).
- The query can be run in the program by HExecuteQuery.
SQL count query on a grouping through programming The SQL queries can be directly written through programming in the code. To do so, you must: - Create a Data Source variable to represent the query at runtime
- Create a Character String variable to contain the SQL code and write the SQL code in this variable
- Run the SQL query with HExecuteSQLQuery
- 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
This page is also available for…
|
|
|
|