expression problem

[Roberto_Civita]

I have 4 tables:
- ANAGRAFICHE (soccer players)
- Pagamenti(payments made by players)
- Profili Pagamento (Contains the value of how much each player has to pay [FEE])
- Categorie(The category to which each player belongs)

With this view, I grouped the rows by category and used the SUM aggregation function, to see how much individual players paid in relation to their category

What I would like to do is write a query that gives me, for each category, how much they have to pay.

Here is an example
Anagrafiche = Ronaldo, Profilo Pagamento [QUOTA] = 500 €, Category = Manchester United, Payments = 200 €, 100 €
Anagrafiche = Messi, Profilo Pagamento [QUOTA]= 1000 €, Categoria = PSG, Pagamenti= 200 €, 200 €
Anagrafiche = Totti, Profilo Pagamento [QUOTA] = 200 €, Categoria = Rome, Pagamenti= 100 €, 100 €
Anagrafiche = Insigne, Profilo Pagamento [QUOTA] = 500 €, Categoria = Naples, Pagamenti= 200 €, 100 €
Anagrafiche = Dimaria, Profilo Pagamento [QUOTA] = 500 €, Categoria = PSG, Pagamenti= 100 €

The result that I can now get on how much the individual categories paid is the following:

Manchester United = 200 + 100 = € 300
PSG = 200 + 200 + 100 = 500 €
Rome = 100 + 100 = 200 €
Naples = 200 + 100 = 300 €

What I would like to get is how much each category has to pay:

Manchester United = € 500
PSG = 1000 + 500 = 1500 €
Rome = € 200
Naples = 500 €

[WillowMobileSys]

- Profili Pagamento (Contains the value of how much each player has to pay [FEE])

Since the table noted above has the value that each player has to pay AND you assign each player to a Category then this is the table you should be grouping on to get the Amount that has to be paid for Profilo Pagamento at the Category level. 

If this column has been included in your sample view you have shown, you should have a SUM::Profilo Pagamento option in the Grouping choices.  If you do not have this choice then make sure your column Profilo Pagamento has been properly included in the view data.

If your goal is to have BOTH SUM:: IMPORTO and SUM::Profilo Pagamento , you cannot do so in a single view.  You can create 2 views each grouped differently and then include those into a Dashboard so they can be seen side-by-side or on a phone as tabs.

[Roberto_Civita]

thank you , can you help me with the query? the data is in different tables

[WillowMobileSys]

I think I see the issue

If Profilo Pagamento contains how much each PLAYER has to pay, where is the link to the player? 

From the player you should be deriving the Categoria (by the way this is also true in the Pagamenti table if you are not already).  

Once you have that link to the player and have derived the Categoria, then you can Group by Categoria to get your values.

Note:  Since this summed value is in a different table, you can't show it in the same view.  You will need a different view.  You can put both into a Dashboard to see them together.

[Roberto_Civita]

the relationship between Anagrafiche(Player)  and Profilo Pagamento  exists. In the Anagrafiche  data there is the ID Profilo Pagamento.

The relationship is 1 (Profili Pagamento)---> N (Anagrafiche)

for this reason theProfilo Pagamento id has been inserted in the Anagrafica data table

[WillowMobileSys]

I think you need the Ref to Player in your Profilo Pagamento table.

Then you can add the IDCATAGORIA column to your Profilo Pagamento table, derive the value from the player then you can create a view on the that table that is grouped by Categoria and aggregated over the QUOTA values.

[Roberto_Civita]

so do you think i should have a row for each player in the Profilo pagamento table? I have about 300 players and 3 payment profiles.
Entering the ID of the Anagrafiche in the Profilo pagamento  means resolving the relationship between the two tables incorrectly.
Can't get the result as the tables are linked?