MS SQL Server: Count value when value is the only result-CodePudding

I'm trying to count a value, where a specific value is the only result when grouped.

For example counting fruits that equals 2139 is simple enough, however counting fruits that are ONLY 2139 when grouped is proving problematic.

fruit    number
apples   2139
apples   2139
apples   2139
apples   2139
melon    2139
melon    2138
melon    2138
cherry   2139
cherry   2139
grape    2137
grape    2139

My current results (just counting 2139)

apples   4
melon    1
cherry   2
grape    1

My desired outcome

apples   1
cherry   1

CodePudding user response：

Having you tried using except to exclude fruits with numbers other than 2139?

create table dbo.Fruits (
  fruit varchar(10),
  [number] int,
);

insert dbo.Fruits (fruit, [number])
values
  ('apples', 2139),
  ('apples', 2139),
  ('apples', 2139),
  ('apples', 2139),
  ('melon', 2139),
  ('melon', 2138),
  ('melon', 2138),
  ('cherry', 2139),
  ('cherry', 2139),
  ('grape', 2137),
  ('grape', 2139);

select fruit, count(1) as [Count]
from (
  select fruit from dbo.Fruits where [number] = 2139
  except
  select fruit from dbo.Fruits where [number] != 2139
) filtered (fruit)
group by fruit;

CodePudding user response：

WITH CTE(fruit,    number) AS
 (

  SELECT 'apples',   2139 UNION ALL
  SELECT'apples',   2139 UNION ALL
  SELECT'apples',   2139 UNION ALL
  SELECT'apples',   2139 UNION ALL
  SELECT'melon',    2139 UNION ALL
  SELECT'melon',   2138 UNION ALL
  SELECT'melon',    2138 UNION ALL
  SELECT'cherry'  , 2139 UNION ALL
  SELECT'cherry' ,  2139 UNION ALL
  SELECT'grape' ,   2137 UNION ALL
  SELECT'grape',    2139
)
SELECT C.FRUIT
FROM CTE AS C
 GROUP BY C.fruit
 HAVING MIN(C.NUMBER)=MAX(C.NUMBER)

CodePudding user response：

 select t.fruit, count (distinct t.fruit)
 from t 
 where t.number = 2139
 and not exists (
    select * from t t2 
    where t2.fruit=t.fruit and t2.number !=2139
)
 group by t.fruit