All geek questions in one place

SQL - adding if statement counter

I am a relatively new SQL user, so please bear with me :)

My current request is as follows:

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)

I would like it to run the S.SID number (or it could be PS.SID - this is the same data), if PS.DC = 1. If there is PS.DC = 1, then it can return the number 0.

I do not know where to do it or is it correctly written. Current results look like ...

 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

Thoughts?

+4
source share
2 answers

Do you want to count records where PS.DC = 1, but not records where PS.DC is something else?

 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

UPDATED: SUM (CASE ...) is used because PS.DC is a bit that cannot be summed.

+1
source

If the data has only 1 and 0, just SUM it. If the data has different values, and you only want to read 1, consider a CASE statement similar to the following ...

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

Source: https://habr.com/ru/post/1481228/

More articles:


All Articles