CODEWITHSUNDEEP
sqlsql-serversql-server-2008countconditional-statements
DrHouseofSQL
DrHouseofSQL

Reputation: 548

SQL Server counting unrelated conditions

Folks,

I have researched this question first and came up with nothing for my specific issue, I found SUM/CASE which is neat but not exactly what I need. Here is my situation:

I have been asked to report back the total number of people who meet 5 out of 8 conditions. I am having trouble coming up with the best way of doing this. It must be something to do with having a counter for each condition and then adding the counter at the end and returning the count of people who met 5 of the 8 conditions (call them condition a - h)

So can you do a count of a count?

Something like 

if exists (code for condition A) 1 ELSE 0 
if exists (code for condition B) 1 ELSE 0 
etc

sum(count)

Thank you

Upvotes: 0

Views: 90

Answers (4)

DrHouseofSQL
DrHouseofSQL

Reputation: 548

I ended up completing this by using a WITH statement something like this:

WITH

(

Select statement for first condition AS blah

Select statement for second condition AS blah

Select statement for third condition AS blah

Select statement for fourth condition AS blah

Select statement for fifth condition AS blah

Select statement for sixth condition AS blah

Select statement for seventh condition AS blah

Select statement for eighth condition AS blah

)

select

CASE WHEN (8 cases based on the 8 selects above

I just put the results in a spreadsheet and did all the math in Excel

Upvotes: 0

David
David

Reputation: 1061

User defined function to the rescue

SELECT * FROM view_people_with_conditions_count WHERE conditions_count  BETWEEN 5 AND 8

CREATE VIEW view_people_with_conditions_count
AS
SELECT *, dbo.GetCondtionsCount(id) as conditions_count FROM peoples_table

CREATE function GetCondtionsCount(@id int)
RETURNS int
AS
BEGIN
      DECLARE @counter int

      ' implement your conditions here
      IF (condtion1) SET @counter = @counter + 1
      IF (condtion2) SET @counter = @counter + 1
      IF (condtion3) SET @counter = @counter + 1
      RETURN @counter
END

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

Since the conditions are spread across rows, you can do this by combining MAX() and a CASE statement in a HAVING clause:

SELECT person_ID
FROM YourTable
GROUP BY Person_ID
HAVING MAX(CASE WHEN ConditionA THEN 1 END)
     + MAX(CASE WHEN ConditionB THEN 1 END)
     + MAX(CASE WHEN ConditionC THEN 1 END)
     + MAX(CASE WHEN ConditionD THEN 1 END)
     + MAX(CASE WHEN ConditionE THEN 1 END)
     + MAX(CASE WHEN ConditionF THEN 1 END)
     + MAX(CASE WHEN ConditionG THEN 1 END)
     + MAX(CASE WHEN ConditionH THEN 1 END)
    >= 5

Upvotes: 1

Bill Gregg
Bill Gregg

Reputation: 7147

How about:

WHERE CASE WHEN (Code for condition A) then 1 else 0 end + 
      CASE WHEN (Code for condition B) then 1 else 0 end +
      CASE WHEN (Code for condition C) then 1 else 0 end +
      ...
      = 5

Upvotes: 0

Related Questions