Solved

Sum 2 Columns Subtract 2 Columns If Statement?

Posted on 2011-02-28
5
1,008 Views
Last Modified: 2012-05-11
I would like to use the results "dif" to build If Statement. Unsure how to include ">= 0" into following code.
Could need Case statement?

[English]
If sum(col1) - sum(col2 >= 0 Then continue with other code, Else don't continue

if Exists (
Select Sum(Col1) - Sum(Col2) As [diff]
 From Products
 Where SalesMan = 'sales@mycompany.com'
 And ProductName = 'Laptop'
 Group By Col1, Col2
 )
--...continue with more code...


0
Comment
Question by:pointeman
[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
5 Comments
 
LVL 28

Expert Comment

by:strickdd
ID: 35000797
IF (SUM(col1) - SUM(col2)) >= 0
BEGIN
   --Stuff to do here.
END
0
 
LVL 9

Accepted Solution

by:
joshbula earned 167 total points
ID: 35000815
Do the calculation in the WHERE statement:

Select * FROM Products
WHERE Sum(Col1) - Sum(Col2) >= 0 AND SalesMan = ....etc...
0
 
LVL 8

Assisted Solution

by:WesWilson
WesWilson earned 166 total points
ID: 35000857
IF (SELECT Sum(Col1) - Sum(Col2)
 FROM Products
 Where SalesMan = 'sales@mycompany.com'
 And ProductName = 'Laptop') >= 0
--do whatever
SELECT 1
ELSE
SELECT 2
0
 
LVL 19

Assisted Solution

by:billmercer
billmercer earned 167 total points
ID: 35000913
This: sum(col1) - sum(col2 )>= 0  is comparing two values and returning true or false based on which is bigger.
However this: if Exists ( Select Sum(Col1) - Sum(Col2) As [diff]...etc )
is checking to see if a record was returned, regardless of what the values are.

If you want to know if there are any records that match this criterion, wouldn't a where be more appropriate?

if exists ( Select *  From Products  Where SalesMan = 'sales@mycompany.com'
 And ProductName = 'Laptop'
and sum(col1) >= sum(Col2)
 Group By Col1, Col2 )

As an aside, if your goal is to only do something to records that have a certain value for diff, it would probably be more efficient to build a query that does what you need rather than using a cursor to move through records and test each one.  
 
0
 

Author Closing Comment

by:pointeman
ID: 35001301
ya, Where statement
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

630 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