Solved

Need help with SUM and negative values.

Posted on 2014-03-22
7
241 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 48

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
Technology Partners: 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

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!

Question has a verified solution.

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

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…

752 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