SQL -添加一个计数if语句

Question

我是一个相对较新的SQL用户，所以请原谅我:)

我当前的查询如下…

SELECT DISTINCT 
  DMA.Name, 
  CLI.CNum, 
  CLI.LNum, 
  CLI.TSC, 
  CLI.FromDate, 
  CLI.ToDate
FROM PurchasedSites INNER JOIN
  CLI ON PS.CNum = CLI.CNum INNER JOIN
  S ON PS.SID = S.SID INNER JOIN
  DMA ON S.DMA = DMA.ID INNER JOIN
  PSM ON PS.PSID = PSM.PSID INNER JOIN
  C ON CLI.CNum = C.CNum
WHERE (PSM.MT_bmID = 10) AND 
  (CLI.FromDate <= '2013-03-31') AND 
  (CLI.ToDate >= '2013-03-01') AND 
  (S.DMA IN ('134', '113', '38', '147', '169', '24', '50', '198', '9', '55')) AND 
  (C.CS LIKE 'Active') AND 
  (PS.CNum Like 'C%') AND 
  (CLI.TSC >= 1)

如果PS.DC = 1，我希望它计算s.d id的数量(或者它可以是PS.SID -它是相同的数据)。如果存在PS.DC = 1，那么它可以返回一个数字0。

我不知道该把它放在哪里，也不知道该怎么写。当前的结果看起来像…

Name              CNum     LNum   TSC   FromDate     ToDate
Detroit, MI       C147157   22     2    2013-03-18   2013-06-10 
Atlanta, GA       C146525   112    2    2013-01-28   2013-03-03 
Washington, DC    C146538   20     1    2013-02-06   2013-03-05
Los Angeles, CA   C146119   45     3    2013-01-01   2013-11-30 

想法吗?

Answer 1

您的意思是计算PS.DC = 1的条目，而不计算PS.DC为其他任何内容的条目吗?

SELECT DMA.Name, 
CLI.CNum, 
CLI.LNum, 
CLI.TSC, 
CLI.FromDate, 
CLI.ToDate,
SUM(CASE WHEN PS.DC = 1 THEN 1.0 ELSE 0.0 END) AS CountDC
FROM PurchasedSites PS
INNER JOIN CLI ON PS.CNum = CLI.CNum 
INNER JOIN S ON PS.SID = S.SID 
INNER JOIN DMA ON S.DMA = DMA.ID 
INNER JOIN PSM ON PS.PSID = PSM.PSID 
INNER JOIN C ON CLI.CNum = C.CNum
WHERE PSM.MT_bmID = 10
AND CLI.FromDate <= '2013-03-31'
AND CLI.ToDate >= '2013-03-01'
AND S.DMA IN ('134', '113', '38', '147', '169', '24', '50', '198', '9', '55') 
AND C.CS LIKE 'Active'
AND PS.CNum Like 'C%'
AND CLI.TSC >= 1
GROUP BY DMA.Name, 
CLI.CNum, 
CLI.LNum, 
CLI.TSC, 
CLI.FromDate, 
CLI.ToDate

更新:使用SUM(CASE…)，因为PS.DC是位类型，不能求和

Answer 2

如果数据只有1和0，则将其求和。如果数据有其他值，并且您只想计数1，请考虑类似于下面的CASE语句…

SELECT SUM(CASE PS.DC WHEN 1 THEN 1 ELSE 0 END) AS colName