Custom Queries to Database > Example: Using Functions in Queries

Objective: Get statistics on clients by countries (i.e. the number of clients from each country).

1. Drag&drop the tables CLIENTS and COUNTRIES to the working area. In the CLIENTS table check the COUN_ID field, and also COUN_NAME field in the COUNTRIES table.

query_example_3_1

2. Set the connection between the tables CLIENTS and COUNTRIES by unique field COUN_ID: click and hold left mouse button on the COUN _ID field in the CLIENTS field, drag it to the COUN _ID field in the COUNTRIES table, and release the button. As a result you get the following:

query_example_3_2

3. Now you need to specify the selection conditions. Set the necessary sorting order: number of countries (in the CLIENTS table) — descending, names of the countries — ascending). For the COUN_ID field choose the Count function, and also set grouping by country name:

query_example_3_3

4. After the request model was created, click Generate Query button. SQL code of the request will be generated and displayed in Content field of Query tab.

query_example_3_4

5. You can view the results by clicking Run Query button. You will get the table with countries rating according to the number of your clients from each country. For example:

query_example_3_5

6. To save the request model switch to the Query tab, specify the Query Name and Description (optionally) and click OK button.

Note: You can export or print these data from the Custom Queries tab of the Server Administrator Corporate Settings (using Export Data and Print Data buttons) (the query should be saved as described in the previous paragraph).