SQL - Adding a count if Statement

Question

I'm a relatively new SQL user so please bear with me :)

My current query 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 COUNT the number of S.SID (or it could be PS.SID - it's the same data) if PS.DC = 1. If there are PS.DC = 1, then it can return a number of 0.

I'm not sure where to fit this in or have it written properly. 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?

Which DBMS are you using? Postgres? Oracle?

user330315
– user330315

2013-05-16 18:44:33 +00:00
Commented May 16, 2013 at 18:44 — user330315
– user330315, Commented May 16, 2013 at 18:44

criticalfix · Accepted Answer · 2013-05-20 12:05:46Z

1

Do you mean to count the entries where PS.DC = 1, but not the entries where PS.DC is anything 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: Used SUM(CASE...) because PS.DC is a bit type, which cannot be summed.

edited May 20, 2013 at 12:05

answered May 16, 2013 at 17:15

criticalfix

2,8701 gold badge21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user2390790 Over a year ago

Sorry - should have clarified - PS.DC is only either a 1 or a 0, which is why I want to count only the 1's.

criticalfix Over a year ago

@user2390790 I have updated my answer - in this case this shouldn't even need a CASE statement. We can just sum up PS.DC. Then, of course, I realized that I meant SUM() instead of COUNT() all along. COUNT() won't give the right answer. Sorry about that.

criticalfix Over a year ago

Just for reference, the more general approach would have been SUM(CASE WHEN PS.DC = 1 THEN 1 ELSE 0 END) AS NumEntries

user2390790 Over a year ago

This makes sense, however I have it exactly as written and it's given me an error "Operand data type bit is invalid for sum operator" Hmmm

criticalfix Over a year ago

The bit type should still work in the CASE statement, though, so you should be able to use that. If SQL Server still thinks it's a bit type, you could make the output values into floating point numbers, e.g., SUM(CASE WHEN PS.DC = 1 THEN 1.0 ELSE 0.0 END) AS NumEntries. Or else use CAST or CONVERT.

|

Prahalad Gaggar · Accepted Answer · 2013-05-20 12:10:06Z

0

If the data has only 1 and 0 just SUM it. If the data has other values and you only want to count the 1's, consider a CASE statement similar to the following...

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

edited May 20, 2013 at 12:10

Prahalad Gaggar

11.6k17 gold badges58 silver badges73 bronze badges

answered May 16, 2013 at 18:35

dazedandconfused

3,2162 gold badges20 silver badges30 bronze badges

2 Comments

user2390790 Over a year ago

This also makes sense, however when I put this in, it's combining all the PS.DC's from each of the CLI.LNum together for each row (instead of it saying it's correct number, it's adding a 1 for each of the DC's within that LNum, making the number something like 61 instead of 5. Hmmmm...

dazedandconfused Over a year ago

It is probably a fairly simple error in the query, but it is hard to do without a test environment. Can you post the DDL to create the tables and just enough INSERTs to give us some test data? Once we're looking at the same thing, this will go much easier.

Collectives™ on Stack Overflow

SQL - Adding a count if Statement

2 Answers 2

9 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related