Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Sum 2 Columns Subtract 2 Columns If Statement?

Posted on 2011-02-28
Medium Priority
1,015 Views
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
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

LVL 28

Expert Comment

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

LVL 9

Accepted Solution

joshbula earned 668 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

WesWilson earned 664 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

billmercer earned 668 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

ID: 35001301
ya, Where statement
0

## Featured Post

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â€¦
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month9 days, 14 hours left to enroll