CODEWITHSUNDEEP
sqlsql-server
Boris Callens
Boris Callens

Reputation: 93427

Group by with count

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?

Upvotes: 1

Views: 1140

Answers (7)

Quassnoi
Quassnoi

Reputation: 425863

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.

Upvotes: 1

Josh
Josh

Reputation: 6322

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

Upvotes: 0

Steve Broberg
Steve Broberg

Reputation: 4394

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;

Upvotes: 0

Steve Wortham
Steve Wortham

Reputation: 22260

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.

Upvotes: 0

van
van

Reputation: 77092

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

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135181

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

Upvotes: 2

tekBlues
tekBlues

Reputation: 5793

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

Upvotes: 3

Related Questions