Need help with SUM and negative values.

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.
brihol44Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ZberteocConnect With a Mentor Commented:
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
 
PaulConnect With a Mentor Commented:
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
 
Dan CraciunIT ConsultantCommented:
Is by any chance your order_adjustment field of type ENUM?

Please post your CREATE TABLE statement.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Cornelia YoderArtistCommented:
Make sure that every field that can contain a negative value has the SIGNED attribute.
0
 
brihol44Author Commented:
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
 
Dan CraciunConnect With a Mentor IT ConsultantCommented:
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
 
brihol44Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.