I am having an issue trying to figure out how to build a proper MySQL query.
I am creating a PHP script that builds reports in a PDF file. This particular PDF report needs to show me the name of the salesperson, the number of the customer in our system, the invoiced sales amount, any extra charges we have applied to the sale as well as sales tax amounts.
I have gotten pretty far in setting this up, however I am stuck on the last part. I also need to figure out the totals by customer. I need to add up all of the sales amount records per customer that are under 1000 and over 1000. I am not sure how to do that or if that is even possible.
Here is my current SQL script:
SELECT Salesperson_1 as salesman, Customer_Number as customer, SUM(Sales_Amount) as invoiced_sales, Extra_Charge_Amount as extra_charge, Sales_Tax_Amounts as tax, (SELECT Customer_Number, Sales_Amount FROM invoices WHERE Sales_Amount > 1000) as total_above_1000, (SELECT Customer_Number, Sales_Amount FROM invoices WHERE Sales_Amount < 1000) as total_below_1000 FROM invoices GROUP BY Customer_Number ORDER BY Salesperson_1 ASC, Customer_Number
This is the overall idea of what I am looking for. Maybe I’m overthinking this, I have no clue. I can share more information about it if I need to, but I am limited on what I can share as this is a business application with classified information.