MySQL Getting two different total amounts based on criteria

Databases
#1

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.

#2

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…

  1. Any invoice has its own salesperson. That means you can’t to bind salesperson with customer.

  2. How about sales amount that equal 1000?

#3

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:

Query 1: 7,228.83 + 2,077.38 + 2,541.25 + 2,142.02 = ?
Query 2: 0 + 0 = ?

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.

#4

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.

#5 
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.

#6

You forgot your GROUP BY clause.

#7

Ah… of course.

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?

image
image735Ă—76 6.14 KB

#8

What does this return?

SELECT * FROM invoices WHERE Customer_Number IS NULL;

#9

It returns one NULL record:

image
image1036Ă—75 8.06 KB

#10

And that will be why you’ve got a null row. Somewhere somehow something’s injected a null row into your data set.

The SUM of NULL is 0.

#11

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?)

#12

sorry, you’re thinking of COUNT

the SUM of NULL is NULL

1 Like
#13

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
#14

WITH ROLLUP has syntax problems in my query.

Also, why is it necessary?

#15

it’s been around since at least version 5.6… what version are you on?

it gives you customer totals for each salesperson

#16

The latest… 7.4

Here is my SQL code so far:

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?

#17

no, sorry, it’s 8.0.21

i don’t think there ever was a 7.4

are you sure you’re using MySQL like you said in post #1?

replace the ORDER BY clause with it

and reverse the sequence of columns in the GROUP BY

#18

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.

#19

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   */

can you see the difference?

#20

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):

image

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.