SQL - When Case should not check for result when no case matches?

Question

I want no results for null when nothing matches

select t.range as [PriceRange], count(1) as [BooksCount]
from (
  select case  
    when  Offer > 3  and offer < 5 
    then 'Criteria1'
    when offer >= 5 then 'Criteria2'
    
    END as range    
  from BookDetailsMaster) t
group by t.range

This query return value for null range also, because of which it is taking long time to execute. I want it should not search anything in database if no case matches.

If I apply a filter where t.range is not null it is not returning the null values but the query is taking long, so I am doubting it is still checking for no match criteria but while returning result it is not returning that value.

Id Offer
1    2
2    4
3    5
4    6
5    7
6    1

PriceRange  BooksCount
-------------------------
Criteria1   1
Criteria2   3

The doubt is it is also making query for a null criteria

Answer 1

You should do this simple condition

SELECT t.RANGE AS [PriceRange], COUNT(1) AS [BooksCount]
FROM 
(
  SELECT 
    CASE  
        WHEN  Offer > 3  AND offer < 5  THEN 'Criteria1'
        WHEN offer >= 5 THEN 'Criteria2'
    END AS RANGE    
  FROM BookDetailsMaster
  WHERE offer > 3
) t
GROUP BY t.RANGE;

Answer 2

if you don't want to use where then you can try below option

SELECT SUM(CASE WHEN (OFFER >3 AND OFFER<5) THEN 1 ELSE 0 END) [BooksCount], 'Criteria1'  [PriceRange]
FROM BookDetailsMaster
UNION 
SELECT SUM(CASE  WHEN (offer >= 5) THEN 1 ELSE 0 END), 'Criteria2'
FROM BookDetailsMaster

Answer 3

You are already defining range in a subquery. Just use it for filtering in the outer query:

select t.range as PriceRange, count(1) as BooksCount
from (select (case when  Offer > 3 and offer < 5 then 'Criteria1'
                   when offer >= 5 then 'Criteria2'        
              end) as range    
      from BookDetailsMaster
     ) t
where t.range is not null
group by t.range