• Status: Solved
• Priority: Medium
• Security: Public
• Views: 278

# How to use CASE

Hi I have a query below

SELECT     contract.contractid, SUM(vw_job_newtailcount.r1b + vw_job_newtailcount.r1c) AS Expr1
FROM         job INNER JOIN
contract ON job.contractid = contract.contractid INNER JOIN
vw_job_newtailcount ON job.jobid = vw_job_newtailcount.jobid
WHERE     (job.statusid NOT IN (3, 4, 5, 6, 7)) AND (contract.contractid IN (5, 6, 12, 13))
GROUP BY contract.contractid

I now want to produce a select statement as follows

select SUM(vw_job_newtailcount.r1b + vw_job_newtailcount.r1c) when contract.contractid in (5,12) as AAP

How can I do this

Thanks

Mark
0
polynominal
1 Solution

Hi polynominal,

Use:

SELECT     contract.contractid, SUM(vw_job_newtailcount.r1b + vw_job_newtailcount.r1c) AS Expr1,
Sum(Case When Contract.ContractID IN (5,12) Then vw_job_newtailcount.r1b+vw_job_newtailcount.r1c Else 0 End) As AAP
FROM         job INNER JOIN
contract ON job.contractid = contract.contractid INNER JOIN
vw_job_newtailcount ON job.jobid = vw_job_newtailcount.jobid
WHERE     (job.statusid NOT IN (3, 4, 5, 6, 7)) AND (contract.contractid IN (5, 6, 12, 13))
GROUP BY contract.contractid

Tim Cottee
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.