CODEWITHSUNDEEP
sqlsql-servert-sql
Pikachu620
Pikachu620

Reputation: 483

How many criteria matched?

If I use OR to string together while conditions, it will return results that fit any of it, right?

For example, if I type

select * from TestTable
where Color='White' or Material='Cotton' or Gender='Female'
      or Length='Short'

It will give me results from 1 match to all 4 conditions matched, right?

Is there any way to know how many conditions are matched for each return results?

Upvotes: 1

Views: 55

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46193

One method is with a CASE expression along with a SUM windowing function:

CREATE TABLE dbo.testtable(
      Color varchar(10)
    , Material varchar(10)
    , Gender varchar(10)
    , Length varchar(10)
);

INSERT INTO dbo.testtable VALUES
      ('White', 'Cotton', 'Female', 'Short')
    , ('White', 'Cotton', 'Female', 'Long')
    , ('White', 'Cotton', 'Male', 'Long')
    , ('White', 'Nylon', 'Male', 'Long')

SELECT *
    ,SUM(CASE WHEN Color='White' THEN 1 END) OVER(ORDER BY (SELECT 0)) AS ColorMatches 
    ,SUM(CASE WHEN Material='Cotton' THEN 1 END) OVER(ORDER BY (SELECT 0)) AS MaterialMatches 
    ,SUM(CASE WHEN Gender='Female' THEN 1 END) OVER(ORDER BY (SELECT 0)) AS GenderMatches
    ,SUM(CASE WHEN Length='Short' THEN 1 END) OVER(ORDER BY (SELECT 0)) AS LengthMatches
FROM TestTable
WHERE 
       Color='White'
    OR Material='Cotton' 
    OR Gender='Female'
    OR Length='Short';

Upvotes: 2

Thom A
Thom A

Reputation: 95544

You'd need to use a CASE expression in your SELECT. Maybe something like:

CASE Color WHEN 'White' THEN 1 ELSE 0 END + 
CASE Material WHEN 'Cotton' THEN 1 ELSE 0 END + 
CASE Gender WHEN 'Female' THEN 1 ELSE 0 END + 
CASE Length WHEN 'Short' THEN 1 ELSE 0 END AS WhereMatched

Upvotes: 4

Related Questions