Solved

Need help with SUM and negative values.

Posted on 2014-03-22
7
240 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
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:yodercm
ID: 39948502
Make sure that every field that can contain a negative value has the SIGNED attribute.
0
Independent Software Vendors: 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 26

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

685 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