• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • 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
 
TimCotteeHead of Software ServicesCommented:
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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