Help Needed

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
webnestors
Posts: 2
Joined: 2023-05-17 21:02

Help Needed

Post by webnestors » 2024-09-26 11:42

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?

User avatar
rngoda
Veteran Member
Posts: 152
Joined: 2020-02-05 16:00
Location: KENYA
Contact:

Re: Help Needed

Post by rngoda » 2025-01-28 07:39

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.
  • 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.
Here is an adjusted query that should work for your requirement:

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%'
Let's go through the key points:
  • 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)).
If you encounter any issues with the query, please let me know, and I would be happy to assist further.
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

Post Reply