Group by with count

Question

Say I have a table like this in my MsSql server 2005 server

Apples
 + Id
 + Brand
 + HasWorms

Now I want an overview of the number of apples that have worms in them per brand.
Actually even better would be a list of all the apple brands with a flag if they are unspoiled or not.

So if I had the data

ID| Brand        | HasWorms
---------------------------
1 | Granny Smith | 1
2 | Granny Smith | 0
3 | Granny Smith | 1
4 | Jonagold     | 0
5 | Jonagold     | 0
6 | Gala         | 1
7 | Gala         | 1

I want to end up with

Brand        | IsUnspoiled
--------------------------
Granny Smith | 0
Jonagold     | 1
Gala         | 0

I figure I should first

select brand, numberOfSpoiles = 
    case
        when count([someMagic]) > 0 then 1
        else 0
     end
from apples
group by brand

I can't use a having clause, because then brands without valid entries would dissapear from my list (I wouldn't see the entry Gala).
Then I thought a subquery of some kind should do it, but then I can't link the apple id of the outer (grouped) query to the inner (count) query...

Any ideas?

Answer 1

select brand,  case when sum(hasworms)>0 then 0 else 1 end IsUnSpoiled
from apples
group by brand

Answer 2

SELECT  brand,
        COALESCE(
        (
        SELECT  TOP 1 0
        FROM    apples ai
        WHERE   ai.brand = ao.brand
                AND hasWorms = 1
        ), 1) AS isUnspoiled
FROM    (
        SELECT  DISTINCT brand
        FROM    apples
        ) ao

If you have an index on (brand, hasWorms), this query will be super fast, since it does not count aggregates, but instead searches for a first spoiled apple within each brand ans stops.

Answer 3

SELECT CASE WHEN SUM(HasWorms) > 0 THEN 0 ELSE 1 END AS IsUnspoiled, Brand FROM apples GROUP BY Brand

Answer 4

SQL server version, I did spoiled instead of unspoiled, this way I could use the SIGN function and make the code shorter

table + data (DML + DDL)

create table Apples(id int,brand varchar(20),HasWorms bit)

insert Apples values(1,'Granny Smith',1)
insert Apples values(2,'Granny Smith',0)
insert Apples values(3,'Granny Smith',1)
insert Apples values(4,'Jonagold',0)
insert Apples values(5,'Jonagold',0)
insert Apples values(6,'Gala',1)
insert Apples values(7,'Gala',1)

Query

select brand, IsSpoiled = sign(sum(convert(int,hasworms)))
from apples
group by brand

Output

brand   IsSpoiled
---------------------- 
Gala    1
Granny Smith    1
Jonagold    0

Answer 5

SELECT  Brand, 
        1-MAX(HasWorms) AS IsUnspoiled
FROM    apples
GROUP BY Brand

Answer 6

I haven't tested this, and maybe I'm missing something. But wouldn't this work?

SELECT Brand, SUM(CONVERT(int, HasWorms)) AS SpoiledCount
FROM Apples
GROUP BY Brand
ORDER BY SpoiledCount DESC

I assume HasWorms is a bit field, hence the CONVERT statement. This should return a list of brands with the count of spoiled apples per brand. You should see the worst (most spoiled) at the top and the best at the bottom.

Answer 7

There are many ways to skin this cat. Depending on your RDBMS, different queries will give you the best results. On our Oracle box, this query performs faster than all the others listed, assuming that you have an index on Brand in the Apples table (an index on Brand, HasWorms is even faster, but that may not be likely; depending on your data distribution, an index on just HasWorms may be the fastest of all). It also assumes you have a table "BrandTable", which just has the brands:

SELECT Brand
     , 1 IsSpoiled
  FROM BrandTable b
 WHERE EXISTS
       ( SELECT 1
           FROM Apples a
          WHERE a.brand = b.brand
            AND a.HasWorms = 1
       )
UNION
SELECT Brand
     , 0
  FROM BrandTable b
 WHERE NOT EXISTS
       ( SELECT 1
           FROM Apples a
          WHERE a.brand = b.brand
            AND a.HasWorms = 1
       )
ORDER BY 1;