Solved

Sum 2 Columns Subtract 2 Columns If Statement?

Posted on 2011-02-28
5
999 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to resize column length with primary ket 4 34
SQL Job Failed 6 31
Enable TLS 1.2 for SQL 2012 Web Edition 1 25
SQL - Using an 'array' kind of variable 5 19
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

710 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