MySQL Create Commission Reports per Salesperson

https://quickforget.com/s/892ee7953cae3529deef2104987f56c01c57000aac59ffd0

It’s called KeyedIn Manufacturing (https://www.keyedin.com/)

As for what the company changes, see my previous response.

Data link is no good. I am also looking into the software itself to see what capabilities are available.

Check your DM. I sent it to you there so no one else can see it but you.

Good luck. The company tells me that they are very complicated to work with…

Perhaps it may be better for them to have a custom ERP built. It would be cheaper in the long run. Problems with these SASS apps is they often dont work like the way you run your own business and as your client says, they can be hard to work with. They are also giving access to their proprietary data to a third party.

In this case, I agree, however the company will not switch software. They are sticking to it. So, they asked me to code them a simple program that handles commission and sales so the accountant’s tasks in calculating it will be easier.

The overall program is not that bad, it can be a bit glitchy at times, but it’s the best thing available for them to use.

It appears the Software has an API. Are you able to get the documentation for that?

I’m afraid I can’t. Is it possible to use a SUM function on two alias columns?

yes and no – it depends

please show the query where you need to do this

That query is what I’m trying to figure out. I need to add these two parts together to make one column:

CASE WHEN Sales_Amount >= 1000 THEN ROUND(Sales_Amount * 0.09, 2) ELSE 0.00 END as comm_above_1000,
CASE WHEN Sales_Amount < 1000 THEN ROUND(Sales_Amount * 0.045, 2) ELSE 0.00 END as comm_below_1000 

This is called CTE. Basically you create a list and then do the query based on this list. Normally this simplify complicated queries. Works from MySQL 8 and above.

Note that this is not an exact solution. But a hint that this could be done using SQL.

  CASE WHEN Sales_Amount >= 1000 
       THEN ROUND(Sales_Amount * 0.09, 2) 
       ELSE 0.00 END                         as comm_above_1000
, CASE WHEN Sales_Amount < 1000 
       THEN ROUND(Sales_Amount * 0.045, 2) 
       ELSE 0.00 END                         as comm_below_1000 
, CASE WHEN Sales_Amount >= 1000 
       THEN ROUND(Sales_Amount * 0.09, 2) 
       ELSE 0.00 END
+ CASE WHEN Sales_Amount < 1000 
       THEN ROUND(Sales_Amount * 0.045, 2) 
       ELSE 0.00 END                         as comm_above_and_below 
1 Like

@r937 That produced the result I needed, but how do I take these totals:

image

and add all of them up to get one grand total value? I need to do this for both the sales value and the commission value.

I tried using WITH ROLLUP but that doesn’t add up the columns properly.

sorry, i can’t see your query, but if the totals aren’t right, you did something wrong

Here is the query I used after I added the statement you provided:

SELECT Customer_Number as customer, 
LEFT(Customer_Name, 28) as name, 
SUM(Sales_Amount) as invoiced_sales, 
SUM(CASE WHEN Sales_Amount >= 1000 THEN Sales_Amount ELSE 0 END) as total_above_1000, 
CASE WHEN Sales_Amount >= 1000 THEN ROUND(Sales_Amount * 0.09, 2) ELSE 0.00 END as comm_above_1000, 
SUM(CASE WHEN Sales_Amount < 1000 THEN Sales_Amount ELSE 0 END) as total_below_1000, 
CASE WHEN Sales_Amount < 1000 THEN ROUND(Sales_Amount * 0.045, 2) ELSE 0.00 END as comm_below_1000, 
CASE WHEN Sales_Amount >= 1000 THEN ROUND(Sales_Amount * 0.09, 2) ELSE 0.00 END + CASE WHEN Sales_Amount < 1000 THEN ROUND(Sales_Amount * 0.045, 2) ELSE 0.00 END as total_comm 
FROM invoices 
WHERE Balance_Due = 0 AND Salesperson_1 = 'BK' AND Sales_Amount != 0 
GROUP BY Salesperson_1, Customer_Number

why are some of those calculations inside SUM() functions and others aren’t?

there’s your problem right there

Should SUM() not be used?

SELECT Customer_Number as customer, 
LEFT(Customer_Name, 28) as name, 
Sales_Amount as invoiced_sales, 
CASE WHEN Sales_Amount >= 1000 THEN Sales_Amount ELSE 0 END as total_above_1000, 
CASE WHEN Sales_Amount >= 1000 THEN ROUND(Sales_Amount * 0.09, 2) ELSE 0.00 END as comm_above_1000, 
CASE WHEN Sales_Amount < 1000 THEN Sales_Amount ELSE 0 END as total_below_1000, 
CASE WHEN Sales_Amount < 1000 THEN ROUND(Sales_Amount * 0.045, 2) ELSE 0.00 END as comm_below_1000, 
CASE WHEN Sales_Amount >= 1000 THEN ROUND(Sales_Amount * 0.09, 2) ELSE 0.00 END + CASE WHEN Sales_Amount < 1000 THEN ROUND(Sales_Amount * 0.045, 2) ELSE 0.00 END as total_comm 
FROM invoices 
WHERE Balance_Due = 0 AND Salesperson_1 = 'BK' AND Sales_Amount != 0
GROUP BY Salesperson_1, Customer_Number

not if you don’t want totals by customer… otherwise yes

That’s the idea of this totals section. It needs to list the total sales per customer, and the total commission. But now, I need to add those totals up for a grand total.

But if I remove all the SUM() input from the query, the individual values don’t change, which is good, I guess.

why would you do that if you want totals?

I know I need SUM() but I need a sum of that SUM()

So do all of them need SUM() then?