AppGini Community Forums

Hi all, I have a project with commissions and what I need is an SQL Query that will do this
On the table Visitors I have the Partner field (Lookup field) and the Total Commission Field (Calculated field with this query
SELECT CAST(`Visitors`.`Tour_Commision` + `Visitors`.`Sales_Commision` AS DECIMAL(10, 2)) FROM `Visitors`
WHERE `Visitors`.`id`='%ID%')

On the table Payments I also have the Partner field (Lookup field) and a amount field that I put there the amount that the partner was paid, also I have a calculated field called Remaining Commission
I would like in the remaining commission field to calculate the total commission amount of the visitors table and also the total amount paid from the payments table and put the remaining commission.
This is the query that I wrote but it not working
SELECT
-- p.ID,
-- p.Partner,
-- p.Amount,
-- COALESCE(v.Total_Commision, 0) AS Total_Commision,
COALESCE(SUM(v.Total_Commision) OVER (PARTITION BY p.Partner), 0) - COALESCE(SUM(p.Amount) OVER (PARTITION BY p.Partner), 0)
FROM
Payments p
LEFT JOIN
(SELECT
Partner,
SUM(Total_Commision) AS Total_Commision
FROM
Visitors
GROUP BY
Partner) v ON p.Partner = v.Partner
WHERE p.ID = '%ID%'

Any help?