CODEWITHSUNDEEP
sqlsql-server
MS.
MS.

Reputation: 175

SQL case when with equality and count conditions

How can i perform a query with filtering both value and count of the element.Such as 

 Select 
 (case 
  when element = 'data1' and (select count(element) from mytable where element='data1') > 15 then '1'
  when element = 'data2' and (select count(element) from mytable where element='data2') > 15 then '2'
            .
            .
            .

  )
    from mytable
    where conditions

are there any quick and simple ways to implement this?

Upvotes: 1

Views: 416

Answers (3)

Jader Gabriel Soares de Arruda
Jader Gabriel Soares de Arruda

Reputation: 56

For both code clarity and performance reason, I would separate the aggregating in a CTE and then invoke it in a join. If the table is big perhaps make sense you put the result in a temporary table instead of CTE for performance reasons.

;WITH ElementCTE 
AS
(
    SELECT element, count(Element) AS count_Element 
    FROM mytable 
    GROUP BY element
    WHERE Conditions
)
SELECT 
CASE ELEMENT
    WHEN 'Data1' AND count_Element > 15 THEN '1'
    WHEN 'Data2' AND count_Element > 15 THEN '2'
FROM mytable AS mt
INNER JOIN Element AS el
ON mt.Element = el.Element
WHERE mt.conditions

Upvotes: 1

StepUp
StepUp

Reputation: 38154

Assuming you have a table:

CREATE TABLE NULLTEST
(
TransactioNo INT, 
Code VARCHAR(25)
)

INSERT INTO NULLTEST VALUES (NULL, 'TEST1');
INSERT INTO NULLTEST VALUES (NULL, 'TEST2');
INSERT INTO NULLTEST VALUES (1, 'TEST2');

The query could look like this:

SELECT 
CASE
    WHEN Code = 'TEST2' AND 
        (SELECT COUNT(1) FROM dbo.NULLTEST n WHERE n.Code = 'TEST2')> 1 THEN '1'            
    WHEN Code = 'TEST1' AND 
        (SELECT COUNT(1)  FROM dbo.NULLTEST n WHERE n.Code ='TEST1')> 1 THEN '2'
    ELSE '3' end Yourcolumn
FROM dbo.NULLTEST t
WHERE ...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270351

I think you want window functions:

select (case when element = 'data1' and
                  count(*) over (partition by element) > 15
             then '1'
             when element = 'data2' and
                  count(*) over (partition by element) > 15
             then '2'
        .
        .
        .

     )
from mytable
where conditions

Upvotes: 2

Related Questions