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?
Help Needed
Re: Help Needed
It seems like you're on the right track, but there are a few adjustments needed to get the desired result. Let's break down the requirements and then provide the correct SQL query step by step.
Let's go through the key points:
- You need to calculate the total commission from the Visitors table for each partner.
- You need to calculate the total amount paid from the Payments table for each partner.
- You need to calculate the remaining commission by subtracting the total amount paid from the total commission for each partner.
Code: Select all
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) AS Remaining_Commission
FROM
Payments p
LEFT JOIN
(SELECT
Partner,
SUM(CAST(Tour_Commision + Sales_Commision AS DECIMAL(10, 2))) AS Total_Commision
FROM
Visitors
GROUP BY
Partner) v ON p.Partner = v.Partner
WHERE
p.ID = '%ID%'
- Total Commission Calculation: The subquery (SELECT Partner, SUM(CAST(Tour_Commision + Sales_Commision AS DECIMAL(10, 2))) AS Total_Commision FROM Visitors GROUP BY Partner) calculates the total commission per partner from the Visitors table.
- LEFT JOIN: We join the result of the above subquery with the Payments table on the Partner field.
- Remaining Commission Calculation: The main query calculates the remaining commission as the difference between the total commissions (SUM(v.Total_Commision) OVER (PARTITION BY p.Partner)) and the total payments (SUM(p.Amount) OVER (PARTITION BY p.Partner)).
I'm a software engineer specializing in web database application development for complex scalable web apps.
Buy Admiro Dashboard Theme For Appgini HERE
Buy AdminLTE Plugin For Appgini: HERE
Buy Cloud Storage Plugin For Appgini HERE
Checkout AdminLTE Plugin For Appgini Tutorials On Youtube
For support Email: [email protected] Whatsappp: Me Here
Buy Admiro Dashboard Theme For Appgini HERE
Buy AdminLTE Plugin For Appgini: HERE
Buy Cloud Storage Plugin For Appgini HERE
Checkout AdminLTE Plugin For Appgini Tutorials On Youtube
For support Email: [email protected] Whatsappp: Me Here