Solved

Need help with SUM and negative values.

Posted on 2014-03-22
7
242 Views
Last Modified: 2014-03-30
Hello, I need help with negative value SUM. So...

SELECT SUM(orders.order_total) - SUM(order_tax + order_shipping + order_adjustment) AS totSpendingHistory
FROM
orders

Open in new window


"order_adjustment" can be + or - values and even when I have say "-1.50" for example it seems to be adding the values and not taking into effect negative.
0
Comment
Question by:brihol44
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 50 total points
ID: 39948239
It depends on how those fields are stored.

IF

order_total, order_tax , order_shipping , order_adjustment
100.00 , -10.00, -10,00, -10.00

then it would need:
SUM(orders.order_total) + SUM(order_tax + order_shipping + order_adjustment)

(notice the plus between the 2 sums)

So without knowing how you store the values it's not possible to be precise.
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39948259
Is by any chance your order_adjustment field of type ENUM?

Please post your CREATE TABLE statement.
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 39948502
Make sure that every field that can contain a negative value has the SIGNED attribute.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:brihol44
ID: 39950359
Thx for the help! I tried all the solutions above.


Problem with changing type to ENUM: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') DEFAULT '0'' at line 1

Tried: SUM(orders.order_total) + SUM(order_tax + order_shipping + order_adjustment)
Problem is that "order_tax" and "order_shipping" will never be negative. "order_adjustment" could be positive or negative though. Or it could be "" or "0"

in Navicat I'm not sure how I have a signed value. I see "Unsigned" and it was not checked so I assume it's "signed"

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `order_id` varchar(75) NOT NULL,
  `order_date` datetime DEFAULT NULL,
  `order_status` int(11) DEFAULT '0',
  `order_customer_id` varchar(50) NOT NULL,
  `order_checkout_type` varchar(20) DEFAULT NULL,
  `order_net_30_due_date` datetime DEFAULT NULL,
  `order_tax` double DEFAULT '0',
  `order_shipping` double DEFAULT '0',
  `order_shipping_tax` double DEFAULT '0',
  `order_total` double DEFAULT '0',
  `order_ship_method_id` int(11) DEFAULT '0',
  `order_ship_date` datetime DEFAULT NULL,
  `order_ship_tracking_id` varchar(100) DEFAULT NULL,
  `order_ship_name` text,
  `order_company` varchar(255) DEFAULT NULL,
  `order_address1` varchar(255) DEFAULT NULL,
  `order_address2` varchar(255) DEFAULT NULL,
  `order_city` varchar(255) DEFAULT NULL,
  `order_state` varchar(50) DEFAULT NULL,
  `order_zip` varchar(50) DEFAULT NULL,
  `order_country` varchar(75) DEFAULT NULL,
  `order_notes` text,
  `order_actual_ship_charge` double DEFAULT '0',
  `order_comments` varchar(255) DEFAULT NULL,
  `order_discount_total` double DEFAULT '0',
  `order_ship_discount_total` double DEFAULT NULL,
  `order_return_date` date DEFAULT NULL,
  `order_return_amount` double DEFAULT NULL,
  `order_adjustment` double(6,0) DEFAULT '0',
  `order_adjustment_note` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `order_customer_id_idx` (`order_customer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Open in new window

0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 400 total points
ID: 39950462
Maybe you wanted this:
SELECT 
	SUM(orders.order_total) - SUM(order_tax + order_shipping) + SUM(order_adjustment) AS totSpendingHistory
FROM
	orders

Open in new window

Or this:
SELECT 
	SUM(orders.order_total+order_adjustment) - SUM(order_tax + order_shipping) AS totSpendingHistory
FROM
	orders

Open in new window

0
 
LVL 35

Assisted Solution

by:Dan Craciun
Dan Craciun earned 50 total points
ID: 39950900
I just tried it on a local XAMPP. Created the table orders, then inserted the following values:
INSERT INTO `orders` (`order_id`, `order_tax`, `order_shipping`, `order_total`, `order_adjustment`) VALUES
('1', 5, 5, 100, -5),
('2', 5, 5, 100, -5);

Open in new window


So basically 2 identical orders, with order_total  = 100, order_tax = order_shipping = 5, order_adjustment = -5

Your query results in totSpendingHistory = 190, which is the expected result.

If you have a negative order_adjustment it will actually increase the totSpendingHistory, because of the - before the parenthesis.

So, if you want to decrease the result of the query, you need order_adjustment to be positive.

HTH,
Dan
0
 

Author Closing Comment

by:brihol44
ID: 39965017
Thx! ... this one seemed to work just fine.

SELECT
      SUM(orders.order_total) - SUM(order_tax + order_shipping) + SUM(order_adjustment) AS totSpendingHistory
FROM
      orders

if order_adjustment was negative or positive.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question