count case when = 1 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

count case when = 1

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-10-24 : 15:30:53

I have the following:

SELECT lg.Name, COUNT(CASE WHEN cs.OpenFlag = 1) AS numOpenCases

FROM CaseHeader AS ch
INNER JOIN CaseDetail cd ON ch.CaseID = cd.CaseHeaderID
INNER JOIN CaseStatus cs ON cs.CaseStatusID = ch.CaseStatusID
INNER JOIN Location l ON ch.LocationID = l.LocationID
INNER JOIN locationGroup lg ON l.PrimaryLocationGroupID = lg.locationGroupID

GROUP BY lg.Name, numOpenCases

You can see I'm trying to get a count from the group by field numOpenCases if the flag = 1.

Have tried a few things, but the syntax is not cooperating.

Suggestions?

Thanks,

Zath

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-10-24 : 15:34:41

SELECT lg.Name, SUM(CASE WHEN cs.OpenFlag = 1 then 1 else 0 end) AS numOpenCases
FROM CaseHeader AS ch
INNER JOIN CaseDetail cd ON ch.CaseID = cd.CaseHeaderID
INNER JOIN CaseStatus cs ON cs.CaseStatusID = ch.CaseStatusID
INNER JOIN Location l ON ch.LocationID = l.LocationID
INNER JOIN locationGroup lg ON l.PrimaryLocationGroupID = lg.locationGroupID

GROUP BY lg.Name, numOpenCases

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-10-24 : 15:46:04

I made some adjustments to get this:

SELECT lg.Name, SUM(CASE WHEN cs.OpenFlag = 1 then 1 else 0 end) AS numOpenCases

FROM CaseHeader AS ch
INNER JOIN CaseDetail cd ON ch.CaseID = cd.CaseHeaderID
INNER JOIN CaseStatus cs ON cs.CaseStatusID = ch.CaseStatusID
INNER JOIN Location l ON ch.LocationID = l.LocationID
INNER JOIN locationGroup lg ON l.PrimaryLocationGroupID = lg.locationGroupID

GROUP BY lg.Name, lg.locationGroupID

But, it's bring back too many for the numOpenCases.

Not shown above, I also have this included in the select:

COUNT(DISTINCT ch.CaseID) AS numOfCases,

And this brought back the exact wrong number UNTIL I added the DISTINCT......

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-10-24 : 15:58:41

quote:
Originally posted by Zath

I have the following:

SELECT lg.Name, COUNT(CASE WHEN cs.OpenFlag = 1) AS numOpenCases

FROM CaseHeader AS ch
INNER JOIN CaseDetail cd ON ch.CaseID = cd.CaseHeaderID
INNER JOIN CaseStatus cs ON cs.CaseStatusID = ch.CaseStatusID
INNER JOIN Location l ON ch.LocationID = l.LocationID
INNER JOIN locationGroup lg ON l.PrimaryLocationGroupID = lg.locationGroupID

GROUP BY lg.Name, numOpenCases

You can see I'm trying to get a count from the group by field numOpenCases if the flag = 1.

Have tried a few things, but the syntax is not cooperating.

Suggestions?

Thanks,

Zath

Maybe I'm missing something here, but you're only asking for two columns (lg.Name, numOpenCases) so your whole query seems strange. I don't see the need for a CASE at all.

Perhaps this instead?


SELECT 
  lg.Name, 
  COUNT(*) AS numOpenCases
FROM 
  CaseHeader AS ch
INNER JOIN 
  CaseDetail cd ON ch.CaseID = cd.CaseHeaderID
INNER JOIN 
  CaseStatus cs ON cs.CaseStatusID = ch.CaseStatusID
INNER JOIN 
  Location l ON ch.LocationID = l.LocationID
INNER JOIN 
  LocationGroup lg ON l.PrimaryLocationGroupID = lg.locationGroupID
WHERE 
  cs.OpenFlag = 1
GROUP BY 
  lg.Name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources