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.
You would to get all invoice sums pro customer, right? Than your query should look like…
SELECT
Customer_Number as customer,
SUM(Sales_Amount) as invoiced_sales,
SUM(Extra_Charge_Amount) as extra_charge,
SUM(Sales_Tax_Amounts) as tax
(SELECT SUM(Sales_Amount) FROM invoices
WHERE Sales_Amount > 1000 AND Customer_Number=customer) as total_above_1000,
(SELECT SUM(Sales_Amount) FROM invoices
WHERE Sales_Amount < 1000 AND Customer_Number=customer) as total_below_1000
FROM invoices
GROUP BY Customer_Number
ORDER BY Customer_Number
Questions…
Any invoice has its own salesperson. That means you can’t to bind salesperson with customer.
Almost, but not quite. The totals_above_1000 and totals_below_1000 are based on the individual records, meaning it has to add up records that are under 1000 and below 1000. In other words, if I’m trying to add records that are above 1000 and some records show amounts that are under 1000, I do not want to add them up.
Here is an example of what I’m trying to achieve. Suppose I have the following sales amounts from ABC Solutions Inc.
$7,228.83 $2,077.38 $2,541.25 $0 $0 $2,142.02
What I need to do is create a query that can do something like this:
Is this possible in MySQL or not, and if it is, how can I create the query based on the idea I gave and the query you made to show the proper result?
I don’t quite understand what you mean, but I’m assuming you’re talking about the GROUP BY Customer_Number in the query and you’re saying it may not be necessary to add this in the overall query. I’m not sure if it is needed or not, I just used it to shorten the overall length of the report.
That will not be necessary as the report does not need to show this.
SUM(CASE WHEN Sales_Amount > 1000 THEN Sales_Amount ELSE 0 END) as total_above_1000
No sub-queries necessary. All of the data is available to the main query.
SELECT Salesperson_1 as salesman,
Customer_Number as customer,
Sales_Amount as invoiced_sales,
Extra_Charge_Amount as extra_charge,
Sales_Tax_Amounts as tax,
SUM(CASE WHEN Sales_Amount > 1000 THEN Sales_Amount ELSE 0 END) as total_above_1000,
SUM(CASE WHEN Sales_Amount < 1000 THEN Sales_Amount ELSE 0 END) as total_below_1000 FROM invoices
ORDER BY Salesperson_1 ASC, Customer_Number
It still doesn’t look right. I need the query to go through the entire table and list the totals per customer, not the sum of the every sales amount in the table.
Also, I’m only seeing one record. It needs to show multiple records as there are multiple sales people and multiple customers.
SELECT Salesperson_1 as salesman,
Customer_Number as customer,
Sales_Amount as invoiced_sales,
Extra_Charge_Amount as extra_charge,
Sales_Tax_Amounts as tax,
SUM(CASE WHEN Sales_Amount > 1000 THEN Sales_Amount ELSE 0 END) as total_above_1000,
SUM(CASE WHEN Sales_Amount < 1000 THEN Sales_Amount ELSE 0 END) as total_below_1000
FROM invoices
GROUP BY Customer_Number
ORDER BY Salesperson_1 ASC, Customer_Number
However, my first record is coming back as a NULL row. Is there a data problem somewhere or does this have something to do with the query?
Also, if you’re trying to get results per-salesman, you need to add that to your GROUP-BY breakdowns. Otherwise querying for Salesperson_1 doesn’t really make sense (Unless only 1 salesperson ever sells to a given customer?)
sorry i’m late to this party but i haven’t seen any query yet that has the appropriate correspondence between what is being GROUPed on and what is being SUMmed
try this
SELECT Salesperson_1 AS salesman
, Customer_Number AS customer
, SUM(Sales_Amount) AS invoiced_sales
, SUM(Extra_Charge_Amount) AS extra_charge
, SUM(Sales_Tax_Amounts) AS tax
, SUM(CASE WHEN Sales_Amount >= 1000
THEN Sales_Amount
ELSE NULL END ) AS total_above_1000
, SUM(CASE WHEN Sales_Amount < 1000
THEN Sales_Amount
ELSE NULL END ) AS total_below_1000
FROM invoices
GROUP
BY Salesperson_1
, Customer_Number
WITH ROLLUP
SELECT Salesperson_1 as salesman,
Customer_Number as customer,
Sales_Amount as invoiced_sales,
Extra_Charge_Amount as extra_charge,
Tax_Amount as tax,
SUM(CASE WHEN Sales_Amount >= 1000 THEN Sales_Amount ELSE 0 END) as total_above_1000,
SUM(CASE WHEN Sales_Amount < 1000 THEN Sales_Amount ELSE 0 END) as total_below_1000
FROM invoices
GROUP BY Customer_Number, Salesperson_1
ORDER BY Salesperson_1 ASC, Customer_Number
Where do I add WITH ROLLUP so that a syntax problem doesn’t happen?
Sorry, yes I am. For some reason I listed my PHP version.
That did it, but why would I not use the ORDER BY clause? It needs to be sorted by Customer Number (smallest to largest) and Salesperson_1 in alphabetical order.
because the WITH ROLLUP syntax does not allow it – check your syntax manual
so you want this –
customer salesperson totals
Smith S02 xxx.xx
Smith S03 xxx.xx
Smith S07 xxx.xx
Smith NULL xxxxx.xx /* subtotal for Smith */
Jones S01 xxx.xx
Jones S04 xxx.xx
Jones S07 xxx.xx
Jones NULL xxxxx.xx /* subtotal for Jones */
NULL NULL xxxxxx.xx /* grand total */
whereas i though you wanted this –
salesperson customer totals
S01 Jones xxx.xx
S01 NULL xxx.xx /* subtotal for S01 */
S02 Smith xxx.xx
S02 NULL xxx.xx /* subtotal for S02 */
S03 Smith xxx.xx
S03 NULL xxx.xx /* subtotal for S03 */
S04 Jones xxx.xx
S04 NULL xxx.xx /* subtotal for S04 */
S07 Smith xxx.xx
S07 Jones xxx.xx
S07 NULL xxx.xx /* subtotal for S07 */
NULL NULL xxxx.xx /* grand total */
I need these two columns in order: Salesperson (alphabetically) and Customer Number (smallest number first). My report already shows that. (These are the salesperson’s initials. There are more salespeople rather than just the ADC data you see here):
The other fields do not need to be in any particular order nor are there any grand totals per salesman in this report. That’s what the invoiced sales and the two total (above and below $1000 respectively) columns are.
