Group data from a query
bamdji
-
DandypunkII Posted messages 70 Status Member -
DandypunkII Posted messages 70 Status Member -
Hello, I'm struggling with a concept in development and I'm developing an application using Windev 18. Since I'm a bit of a novice with this language, I would like to know if it's possible to group data from a query like the "GROUP BY" function in SQL.
If there's a way to do it, please let me know by posting your programming examples.
Thank you.
If there's a way to do it, please let me know by posting your programming examples.
Thank you.
1 answer
Hello,
Let's take the following analysis:
We want to get the total purchases of customers and their number of purchases.
In SQL we would have:
and
With Windev, 2 options are available to you:
- Handle these queries in "text mode" in your code via SQLExec (https://doc.pcsoft.fr/fr-FR/?3072007&name=SQLExec&verdisp=180%29
- Use the query editor.
To do this, you need to create a new query (New/Query).
You have 6 options available:
Select, SQL, Add, Union, Modify and Delete.
We will focus on the Select option.
In area 1, select the desired columns and place them in area 2 either by double-clicking or using the arrows (6)
Once done, select in area 2 the column on which you want to perform the calculation. FACTURE_CLIENT.Total (i.e. FACTURE_CLIENT.PK_FACTURE_CLIENT.)
Area 3 is used to refine the search. Just select Sum (i.e. count), the modification is done in area 2.
You can modify if you wish the names of the columns that will be used (the AS in SQL) via area 4.
In area 5, you have the description of your query in natural language or in SQL.
We validate and your query is available. You can test it via "GO".
The cherry on the cake. You want to see the clients who have no invoices.
In SQL you would write:
With Windev:
A double click on "Join" and just check the appropriate box.
--
If it’s simple, it’s Windev.
Let's take the following analysis:
We want to get the total purchases of customers and their number of purchases.
In SQL we would have:
SELECT CLIENTS.PK_CLIENT AS CLIENT, CLIENTS.NDX_NomClient AS Name, SUM(FACTURE_CLIENTS.Total) AS TotalPurchases FROM CLIENTS, FACTURE_CLIENTS WHERE CLIENTS.PK_CLIENT = FACTURE_CLIENTS.FK_CLIENT GROUP BY CLIENTS.PK_CLIENT, CLIENTS.NDX_NomClient ORDER BY PK_CLIENT ASC
and
SELECT CLIENTS.PK_CLIENT AS CLIENT, CLIENTS.NDX_NomClient AS Name, COUNT(FACTURE_CLIENTS.PK_FACTURE_CLIENTS) AS InvoiceCount FROM CLIENTS, FACTURE_CLIENTS WHERE CLIENTS.PK_CLIENT = FACTURE_CLIENTS.FK_CLIENT GROUP BY CLIENTS.PK_CLIENT, CLIENTS.NDX_NomClient ORDER BY CLIENT ASC
With Windev, 2 options are available to you:
- Handle these queries in "text mode" in your code via SQLExec (https://doc.pcsoft.fr/fr-FR/?3072007&name=SQLExec&verdisp=180%29
- Use the query editor.
To do this, you need to create a new query (New/Query).
You have 6 options available:
Select, SQL, Add, Union, Modify and Delete.
We will focus on the Select option.
In area 1, select the desired columns and place them in area 2 either by double-clicking or using the arrows (6)
Once done, select in area 2 the column on which you want to perform the calculation. FACTURE_CLIENT.Total (i.e. FACTURE_CLIENT.PK_FACTURE_CLIENT.)
Area 3 is used to refine the search. Just select Sum (i.e. count), the modification is done in area 2.
You can modify if you wish the names of the columns that will be used (the AS in SQL) via area 4.
In area 5, you have the description of your query in natural language or in SQL.
We validate and your query is available. You can test it via "GO".
The cherry on the cake. You want to see the clients who have no invoices.
In SQL you would write:
SELECT CLIENTS.PK_CLIENT AS CLIENT, CLIENTS.NDX_NomClient AS Name, SUM(FACTURE_CLIENTS.Total) AS TotalPurchases FROM CLIENTS LEFT OUTER JOIN FACTURE_CLIENTS ON CLIENTS.PK_CLIENT = FACTURE_CLIENTS.FK_CLIENT GROUP BY CLIENTS.PK_CLIENT, CLIENTS.NDX_NomClient ORDER BY PK_CLIENT ASC
With Windev:
A double click on "Join" and just check the appropriate box.
--
If it’s simple, it’s Windev.