[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

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
Asked:
polynominal
1 Solution
 
TimCotteeCommented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now