Hello again,
I have this data:
| CompanyId | GroupId | OrderId |
| C01 | G01 | 1 |
| C01 | G02 | 2 |
| C01 | G05 | 3 |
| C02 | G02 | 1 |
| C02 | G04 | 2 |
| C03 | G01 | 1 |
| C03 | G02 | 2 |
| C03 | G05 | 3 |
| C03 | G06 | 4 |
| C03 | G07 | 5 |
And I need to show a table that will show the number of distinct companies grouped by GroupId. The important thing is that a company has to be counted only once in the available group with the maximum OrderId.
So here you are some examples:
1) With no filters
| GroupId | NumDistinctCompanies |
| G01 | 0 |
| G02 | 0 |
| G03 | 0 |
| G04 | 1 |
| G05 | 1 |
| G06 | 0 |
| G07 | 1 |
| TOTAL | 3 |
2) Filtering groups G01, G02 and G06.
| GroupId | NumDistinctCompanies |
| G01 | 0 |
| G02 | 2 |
| G06 | 1 |
| TOTAL | 3 |
Do not hesiate to ask if you have any doubt and many many thanks in advance.
Javier
