Solved

Need help with SUM and negative values.

Posted on 2014-03-22
7
237 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 34

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 34

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query 8 50
SQL Insert parts by customer 12 34
Query Syntax 17 36
What's wrong with this T-SQL Foreign Key? 7 46
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

810 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