CODEWITHSUNDEEP
sqlsql-serversql-server-2012
samsmom74
samsmom74

Reputation: 3

SUM conditional logic

I have the following 2 tables in SQL Server 2012:

|=================|  
|ID|SALARY|EXCLUDE|  
| 1| 90000|   NULL|  
| 1| 65000|   NULL|  
| 1| 10000|      Y|  
| 2| 85000|   NULL|  
| 2|  4500|      Y|  
| 2|  1000|      Y|  
| 3| 75000|   NULL|  
|=================|

|===========|  
|ID|CONTRACT|  
| 1|  155000|  
| 2|   85000|  
| 3|   75000|  
|===========|

I need to write a query that returns the following results: 

|===========================|  
|ID|CONTRACT|EXCLUDED SALARY|  
| 1|  155000|          10000|  
| 2|   85000|           5500|  
| 3|   75000|           NULL|  
|===========================|

In other words, I need to sum the SALARY field in the first table for each ID if EXCLUDE is Y. I know how to sum the salary column but I don't understand how to include the EXCLUDE logic. Any help you can give me is very much appreciated! Thanks!

Upvotes: 0

Views: 69

Answers (2)

Nizam
Nizam

Reputation: 4699

Try this:

SELECT 
    A.ID, 
    B.CONTRACT, 
    SUM(CASE WHEN EXCLUDE = 'Y' then SALARY END) EXCLUDED_SALARY
FROM TABLE_SALARY A
INNER JOIN TABLE_CONTRACT B
   ON A.ID = B.ID
GROUP BY A.ID, B.CONTRACT

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32392

You can use conditional aggregation to only count salaries where the exclude flag is Y in the sum:

sum(case when exclude = 'Y' then salary end)

Upvotes: 1

Related Questions