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?

@boris: we would love them even more if you told us which RDBMS you are using. — Quassnoi
– Quassnoi, Commented Jun 16, 2009 at 15:37

tekBlues · Accepted Answer · 2009-06-16 15:42:13Z

3

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

edited Jun 16, 2009 at 15:42

answered Jun 16, 2009 at 15:31

tekBlues

5,8031 gold badge32 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Guffa Over a year ago

Except for the casing of the field names and some spacing, it's completely identical to what I arrived at... :)

SQLMenace · Accepted Answer · 2009-06-16 15:43:50Z

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

nice, I didn't even know there was such a function. Also nice that your answer is a standalone answer.

van · Accepted Answer · 2009-06-16 15:43:52Z

1

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

answered Jun 16, 2009 at 15:43

van

77.6k13 gold badges175 silver badges179 bronze badges

Comments

Quassnoi · Accepted Answer · 2009-06-17 10:42:53Z

1

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.

edited Jun 17, 2009 at 10:42

answered Jun 16, 2009 at 15:32

Quassnoi

428k94 gold badges629 silver badges624 bronze badges

1 Comment

Boris Callens Over a year ago

nice solution. The limit function sadly doesn't exist in Tsql, but I forgot to specify that in my original question so that's my mistake.

Steve Wortham · Accepted Answer · 2009-06-16 15:54:37Z

0

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.

edited Jun 16, 2009 at 15:54

answered Jun 16, 2009 at 15:48

Steve Wortham

22.3k5 gold badges73 silver badges92 bronze badges

Comments

Steve Broberg · Accepted Answer · 2009-06-16 16:09:28Z

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;

Boris Callens · Accepted Answer · 2009-06-17 07:24:34Z

0

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

edited Jun 17, 2009 at 7:24

Boris Callens

94k87 gold badges212 silver badges314 bronze badges

answered Jun 16, 2009 at 15:40

Josh

6,2872 gold badges40 silver badges56 bronze badges

Collectives™ on Stack Overflow

Group by with count

7 Answers 7

1 Comment

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Related