SQL- Scenario Based Questions PART- 4

SQL- Scenario Based Questions PART- 4

SCENARIO:

Suppose there is a table which contains a column NetBalance which has both negative and positive values. Now you have to write a query to dispaly sum of all positive Balances and sum of all negative Balances.

 

Consider below BalanceInfo table for answering this scenario based questions:-

BalanceInfo Table

BalanceInfo Table

SOLUTION 1: Using   CASE statement

SELECT SUM(CASE WHEN NetBalance < 0 THEN NetBalance

           ELSE 0 END )As Negative,

       SUM(CASE WHEN NetBalance > 0 THEN NetBalance

           ELSE 0 END )As Positive

FROM dbo.BalanceInfo

SOLUTION 2: Using  SIGN and CASE statement . We know that SIGN operator returns -1 for negative values and 1 for positive values.

SELECT SUM(CASE WHEN SIGN(NetBalance)= -1 THEN NetBalance

           ELSE 0 END )As Negative,

       SUM(CASE WHEN SIGN(NetBalance)= 1 THEN NetBalance

           ELSE 0 END )As Positive

FROM dbo.BalanceInfo

For other SQL Scenario Based Questions, please visit below link

http://itjunction4all.blogspot.com/2014/04/sql-scenario-based-questions-part-2.html