MySQL Getting two different total amounts based on criteria

r937 · July 23, 2020, 7:43pm

okay this is me –

and this is you –

please tell me how we differ

also, just confirm something for me – do you really need to do any SUMming?

jmyrtle · July 23, 2020, 7:51pm

I’m confused now…

Yes. It was part of my OP. I needed totals above and below 1000 per customer and and total invoiced sales column.

What you are referencing is this:

r937:

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

To me, that looks like you are thinking that I want subtotals per salesperson which I’m not looking for at the moment.

r937 · July 23, 2020, 9:43pm

so just to reconfirm your confirm, you don’t want totals, right?

so that means no GROUP BY clause, and no SUM() function

can you take it from here?

m_hutley · July 24, 2020, 10:26am

Where you’re confusing me (dunno about Rudy) is that you include the salesman_1 field in your select.

I’m going to generate some rows of data, since that seems to be the rage lately…

Customer#	SalesPerson	Sale_Amount
1	Jane	1001
1	Smith	894
2	Jane	100
2	Bob	10000

(hooray for markdown.)

If you GROUP BY customer#, and then ask it for SalesPerson and SUM(Sales_Amount)… it might give you this:

Customer#	SalesPerson	SUM(Sale_Amount)
1	Jane	1895
2	Jane	10100

Did Jane do 1895 in sales to customer 1? What about Smith? He was supposed to be on that account too. Slacker!
(The database may also spit Smith’s name out there; and ignore Jane)

So why did we bother selecting the salesperson’s name, if we are going to ignore all but 1 of the salespersons involved?

Do you want data per customer or per customer per salesperson ? Because that’s two different asks. For per customer, it does not make sense to select the salesperson’s name. (You could, however, do something like GROUP_CONCAT to pull all the salespersons names…)

General rule of thumb: When grouping/rolling up, any selected field that has multiple values (read: Any field that isn’t part of the GROUP_BY clause…) needs an aggregation function. SUM, COUNT, MAX, GROUP_CONCAT, etc.

r937 · July 24, 2020, 2:54pm

i have a feeling that @jmyrtle’s data has only one salesperson per customer

but getting this confirmed is proving to be quite the challenge for us, eh?

jmyrtle · July 24, 2020, 3:57pm

I believe this is the correct code I need:

SELECT Salesperson_1 as salesman, 
Customer_Number as customer, 
Customer_Name as name, 
SUM(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 Salesperson_1, Customer_Number 
WITH ROLLUP

r937 · July 24, 2020, 4:42pm

what happened when you tested it? ™

please note you may have to disable ONLY_FULL_GROUP_BY

in your query, Customer_Name is functionally dependent on Customer_Number, but Extra_Charge_Amount and Tax_Amount are not functionally dependent on either of the GROUP BY columns

so out of the box you’ll get a grouping syntax error

jmyrtle · July 24, 2020, 6:58pm

@r937 you know what, I think you might be right. I failed to see this earlier.

I’m getting duplicate records with super big numbers in them and no customer numbers when I run the query. Like this:

This happens for each sales person. I’m sorry for the confusion. And yes, if I can find a way to show the totals for each salesperson, I will need that as well.

r937 · July 24, 2020, 7:37pm

do you have a small sample data set that you could share? couple dozen rows, tops

mysql dump format works great, because it gives you the CREATE TABLE statement as well as INSERT statements, which you can simply edit down to a representative sample

feel free to obfuscate obvious identifying values but give us something to work with, please, otherwise we’re just shooting in the dark

also, please address the problem you still have, which is that you’re SUMming sales amounts but not summing tax amounts – that doesn’t make any sense

r937 · July 24, 2020, 7:52pm

those would be your ROLLUP totals per salesperson

jmyrtle · July 24, 2020, 7:57pm

Is there a way to section this off so it doesn’t show duplicate data from a previous record? If it’s better to create a different query, I can do that.

Here is a sample set of data showing the table structure, sample table data and my query:

/* Table Strutcture */
CREATE TABLE `invoices` (
  `id` int(11) NOT NULL,
  `Invoice_Number` varchar(10) DEFAULT NULL,
  `Customer_Number` int(11) DEFAULT NULL,
  `Customer_Name` varchar(47) DEFAULT NULL,
  `Invoice_Date` date DEFAULT NULL,
  `Sales_Order_Number` varchar(10) DEFAULT NULL,
  `Sales_Amount` decimal(7,2) DEFAULT NULL,
  `Progress_Billing` int(11) DEFAULT NULL,
  `Extra_Charge_Amount` decimal(6,2) DEFAULT NULL,
  `Tax_Amount` decimal(6,2) DEFAULT NULL,
  `Invoice_Amount` decimal(8,2) DEFAULT NULL,
  `Writeoff_Amount` decimal(6,2) DEFAULT NULL,
  `Paid_Amount` decimal(7,2) DEFAULT NULL,
  `Balance_Due` decimal(7,2) DEFAULT NULL,
  `Use_Tax` decimal(6,2) DEFAULT NULL,
  `Tax_Stat` varchar(2) DEFAULT NULL,
  `Salesperson_1` varchar(5) DEFAULT NULL,
  `Comm_Rate_1` int(11) DEFAULT NULL,
  `Salesperson_2` varchar(2) DEFAULT NULL,
  `Comm_Rate_2` int(11) DEFAULT NULL,
  `Salesperson_3` varchar(2) DEFAULT NULL,
  `Comm_Rate_3` int(11) DEFAULT NULL,
  `Deposit_Applied` decimal(7,2) DEFAULT NULL,
  `Sales_Tax_Codes` varchar(17) DEFAULT NULL,
  `Sales_Tax_Amounts` varchar(28) DEFAULT NULL,
  `Customer_Address1` varchar(38) DEFAULT NULL,
  `Customer_Address2` varchar(26) DEFAULT NULL,
  `Customer_City` varchar(17) DEFAULT NULL,
  `Customer_State` varchar(2) DEFAULT NULL,
  `Customer_Zip` varchar(10) DEFAULT NULL,
  `Customer_Country` varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/* Insert Statement */
INSERT INTO `invoices` (`id`, `Invoice_Number`, `Customer_Number`, `Customer_Name`, `Invoice_Date`, `Sales_Order_Number`, `Sales_Amount`, `Progress_Billing`, `Extra_Charge_Amount`, `Tax_Amount`, `Invoice_Amount`, `Writeoff_Amount`, `Paid_Amount`, `Balance_Due`, `Use_Tax`, `Tax_Stat`, `Salesperson_1`, `Comm_Rate_1`, `Salesperson_2`, `Comm_Rate_2`, `Salesperson_3`, `Comm_Rate_3`, `Deposit_Applied`, `Sales_Tax_Codes`, `Sales_Tax_Amounts`, `Customer_Address1`, `Customer_Address2`, `Customer_City`, `Customer_State`, `Customer_Zip`, `Customer_Country`) VALUES
(1, '191230-2', 7172, 'ACME CORPORATION', '2019-11-04', '191230', '6374.89', 0, '0.00', '446.25', '6821.14', '0.00', '6821.14', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '302.81|143.44', '357 CONCRESCERE PARKWAY', 'SUITE 300', 'DAVIDSON', 'NC', '28036', 'US'),
(2, '191230-3', 7172, 'ACME CORPORAITON', '2020-01-23', '191230', '1206.65', 0, '428.75', '84.47', '1719.87', '0.00', '1719.87', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '57.32|27.15', '357 CONCRESCERE PARKWAY', 'SUITE 300', 'DAVIDSON', 'NC', '28036', 'US'),
(3, '200002', 5877, 'MONSTERS INC', '2020-01-14', '200002', '402.25', 0, '0.00', '28.16', '430.41', '0.00', '430.41', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '19.11|9.05', 'WAYNE BROTHERS INCORPORATED', '357 CONCRESCERE PARKWAY', 'DAVIDSON', 'NC', '28036', 'US'),
(4, '200018', 7051, 'UMBRELLA CORPORATION', '2020-01-13', '200018', '675.00', 0, '0.00', '0.00', '675.00', '0.00', '675.00', '0.00', '0.00', '', 'ADC', 5, '', 0, '', 0, '0.00', '', '', '729 PALMER RD', '', 'ROCKWELL', 'NC', '28138', ''),
(5, '200023', 5059, 'GLOBEX CORPORATION', '2020-01-10', '200023', '495.33', 0, '0.00', '34.67', '530.00', '0.00', '530.00', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC056', '23.53|11.14', '3300 FINGER MILL ROAD', '', 'LINCOLNTON', 'NC', '28092', 'US'),
(6, '200029', 7215, 'OCEANIC AIRLINES', '2020-01-13', '200029', '3050.00', 0, '0.00', '213.51', '3263.51', '0.00', '3263.51', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '144.88|68.63', '375 MOOSE RD S', '', 'MT PLEASANT', 'NC', '28124', ''),
(7, '200048', 6650, 'OSCORP INDUSTRIES', '2020-01-22', '200048', '144.60', 0, '0.00', '10.12', '154.72', '0.00', '154.72', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '6.87|3.25', '6420 SADDLE CREEK CT', '', 'HARRISBURG', 'NC', '28075', ''),
(8, '200064', 7227, 'PARKER INDUSTRIES', '2020-01-24', '200064', '1573.52', 0, '0.00', '114.08', '1687.60', '0.00', '1687.60', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC001|NC061', '74.74|7.87|31.47', '1715 ORR INDUSTRIAL COURT', '', 'CHARLOTTE', 'NC', '28213', ''),
(9, '200065', 6679, 'POLYCON', '2020-01-28', '200065', '6287.50', 0, '0.00', '440.13', '6727.63', '0.00', '6727.63', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '298.66|141.47', '735 CONCORD PKWY', '', 'CONCORD', 'NC', '28027', ''),
(10, '200076', 5877, 'MONSTERS INC', '2020-01-22', '200076', '45.00', 0, '0.00', '3.15', '48.15', '0.00', '48.15', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '2.14|1.01', 'WAYNE BROTHERS INCORPORATED', '357 CONCRESCERE PARKWAY', 'DAVIDSON', 'NC', '28036', 'US'),
(11, '200106', 7215, 'ESTEP ELECTRICS', '2020-02-24', '200106', '3050.00', 0, '0.00', '213.51', '3263.51', '0.00', '3263.51', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '144.88|68.63', '375 MOOSE RD S', '', 'MT PLEASANT', 'NC', '28124', ''),
(12, '200152', 5741, 'ABC SOLUTIONS INC', '2020-02-19', '200152', '765.02', 0, '0.00', '55.47', '820.49', '0.00', '820.49', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '820.49', 'NC|NC001|NC061', '36.34|3.83|15.30', 'FOUR S LLC', '550 S CALDWELL ST, STE 755', 'CHARLOTTE', 'NC', '28202', 'US'),
(13, '200157', 7251, 'INDEPENDENT TRIBUNE OF AMERICA', '2020-02-10', '200157', '85.00', 0, '0.00', '5.95', '90.95', '0.00', '90.95', '0.00', '0.00', '', 'ADC', 5, '', 0, '', 0, '0.00', 'NC|NC014', '4.04|1.91', '317 WILSHIRE AVE SW', '', 'CONCORD', 'NC', '28025', ''),
(14, '200174', 6679, 'POLYCON', '2020-02-24', '200174', '4250.00', 0, '0.00', '297.51', '4547.51', '0.00', '4547.51', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '201.88|95.63', '735 CONCORD PKWY', '', 'CONCORD', 'NC', '28027', ''),
(15, '200182', 7260, 'PIPERSTONE', '2020-03-26', '200182', '10364.63', 0, '435.00', '751.43', '11551.06', '0.00', '11551.06', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '5558.03', 'NC|NC001|NC061', '492.32|51.82|207.29', '511 JOHNSON RD', '', 'CHAROTTE', 'NC', '28206', ''),
(16, '200198', 5877, 'MONSTERS INC', '2020-03-17', '200198', '1800.00', 0, '0.00', '126.00', '1926.00', '0.00', '1926.00', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '85.50|40.50', 'WAYNE BROTHERS INCORPORATED', '357 CONCRESCERE PARKWAY', 'DAVIDSON', 'NC', '28036', 'US'),
(17, '200210', 5877, 'MONSTERS INC', '2020-02-25', '200210', '750.00', 0, '0.00', '52.51', '802.51', '0.00', '802.51', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '35.63|16.88', 'WAYNE BROTHERS INCORPORATED', '357 CONCRESCERE PARKWAY', 'DAVIDSON', 'NC', '28036', 'US'),
(18, '200225', 7266, 'BARRDAY', '2020-03-17', '200225', '2143.73', 0, '0.00', '155.42', '2299.15', '0.00', '2299.15', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '1149.58', 'NC|NC001|NC061', '101.83|10.72|42.87', '1450 WEST POINTE DR.', '', 'CHARLOTTE', 'NC', '28214', ''),
(19, '200233', 6363, 'OMNI CONSUMER', '2020-02-28', '200233', '97.00', 0, '3.21', '6.79', '107.00', '0.00', '107.00', '0.00', '0.00', '', 'ADC', 5, '', 0, '', 0, '0.00', 'NC|NC014', '4.61|2.18', '199 WILSHIRE AVE', '', 'CONCORD', 'NC', '28025', 'US'),
(20, '200234', 5877, 'MONSTERS INC', '2020-02-25', '200234', '155.00', 0, '0.00', '10.85', '165.85', '0.00', '165.85', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '7.36|3.49', 'WAYNE BROTHERS INCORPORATED', '357 CONCRESCERE PARKWAY', 'DAVIDSON', 'NC', '28036', 'US'),
(21, '200237', 7268, 'THE PENINSULA AT LAKE WATERNOOSE', '2020-03-17', '200237', '1845.14', 0, '0.00', '129.16', '1974.30', '0.00', '1974.30', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '987.15', 'NC|NC063', '87.64|41.52', '100 CRAIN CT', '', 'MT GILEAD', 'NC', '27306', ''),
(22, '200241', 5135, 'PRESTIGE CAPITAL', '2020-03-17', '200241', '424.50', 0, '0.00', '29.71', '454.21', '0.00', '454.21', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '20.16|9.55', '3030 S CANNON BLVD', '', 'KANNAPOLIS', 'NC', '28083', 'US'),
(23, '200271', 5877, 'MONSTERS INC', '2020-03-17', '200271', '155.00', 0, '0.00', '10.85', '165.85', '0.00', '165.85', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '7.36|3.49', 'WAYNE BROTHERS INCORPORATED', '357 CONCRESCERE PARKWAY', 'DAVIDSON', 'NC', '28036', 'US'),
(24, '200275', 6650, 'OSCORP INDUSTRIES', '2020-03-17', '200275', '477.68', 0, '0.00', '33.44', '511.12', '0.00', '511.12', '0.00', '0.00', '', 'ADC', 5, 'CT', 0, '', 0, '0.00', 'NC|NC014', '22.69|10.75', '6420 SADDLE CREEK CT', '', 'HARRISBURG', 'NC', '28075', ''),

/* Query */
SELECT Salesperson_1 as salesman,
Customer_Number as customer,
LEFT(Customer_Name, 28) as name,
SUM(Sales_Amount) as invoiced_sales,
SUM(Extra_Charge_Amount) as extra_charge,
SUM(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 Salesperson_1, Customer_Number WITH ROLLUP;

I will say that some of this has been modified. I changed the query a little bit to allow the sums of tax amounts and only select a few characters of the customer’s name (this is for PDF purposes in the application) as well as added an id column for purposes of a primary key.

r937 · July 24, 2020, 8:17pm

the CREATE TABLE statement and a couple dozen INSERT statements can be pasted right here, no need to go elsewhere

couple dozen, no more, okay? but make sure they are representative

jmyrtle · July 24, 2020, 8:19pm

See my previous post. It shows the the create table statement and insert statements as well as my query

r937 · July 25, 2020, 2:51pm

thank you … you must’ve edited your post while i was replying to the fiddle

so i loaded your data, and i ran your query, and i don’t see any problem – no duplicate rows, and no outrageous large amounts

congrats!!

also, partly as an aside to @m_hutley, it’s now clear that yes, a customer can have up to three salespersons

jmyrtle · July 27, 2020, 11:43am

This query:

SELECT Salesperson_1 as salesman,
Customer_Number as customer,
LEFT(Customer_Name, 28) as name,
SUM(Sales_Amount) as invoiced_sales,
SUM(Extra_Charge_Amount) as extra_charge,
SUM(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 Salesperson_1, Customer_Number WITH ROLLUP;

is still coming back with larger numbers and duplicate rows since nothing really changed other than the sample data that was provided. I’m thinking it’s part of the rollup like you mentioned, but is there a way to change the format to where it doesn’t give the customer name twice? From this:

to

ADC      7277      SNOW JOE / SUN JOE      8283.35      0.00      579.84      8283.35     0.00
ADC      TOTAL             null            200188.59  2598.69   6555.74    189568.24     10620.35

m_hutley · July 27, 2020, 1:19pm

I don’t see why it would be generating numbers larger than the dataset?

The customer name will appear in each group’s row; you can modify the behavior of the rollup rows with GROUPING checks:

IF(GROUPING(Salesperson_1), 'TOTAL', Salesperson_1) AS salesman

(Basically, GROUPING(x) returns 1 if the row in question is a rollup row in which the x value would be NULL because it represents something that was being grouped on)

r937 · July 27, 2020, 1:33pm

interesting case study on what happens when you mix 1) functionally dependent columns included in SELECT but not GROUP BY, with 2) ROLLUP in the GROUP BY

try adding that LEFT expression to the GROUP BY – i’m guessing you’ll get additional levels of subtotals

sigh

sooo many ways to skin this cat

jmyrtle · July 27, 2020, 1:36pm

I think what I’m gonna do is create a separate query and include it at the bottom of the page.

Like this:

SELECT Salesperson_1, 
SUM(Sales_Amount) as total_sales, 
SUM(Extra_Charge_Amount) as extra_charge_total, 
SUM(Tax_Amount) as total_tax 
FROM `invoices` 
GROUP BY Salesperson_1

That way I can remove the rollup from the previous query and not make it too confusing.

r937 · July 27, 2020, 1:38pm

off the top of my head

detect null in customer number and ignore left(name) value
(hack) concat customer number and left(name) into one string, and group/rollup on that
forget ROLLUP and do subtotals in your front end language

jmyrtle · July 27, 2020, 1:40pm

I went with Option 3. It seems the easiest and quickest way. See my previous response.