CODEWITHSUNDEEP
sqlcountgroup-by
Javier Novoa C.
Javier Novoa C.

Reputation: 11837

SQL count occurrences of certain categories that rows belong to

I have this table with some data:

table ColorsFlavors

id | name | color | flavor
--------------------------
 1 | n1   | green | lemon
 2 | n2   | blue  | strawberry
 3 | n3   | red   | lemon
 4 | n4   | green | lemon
 5 | n5   | green | mango
 6 | n6   | red   | chocolate
 7 | n7   | white | lemon
 8 | n8   | blue  | mango
 9 | n9   | green | chocolate

I wish to make a SQL query (or queries?) that lets me get the total number of rows with each color, and the total number of rows with each flavour.

Something like this:

colors | occurrences
--------------------
green  |   4
blue   |   2
red    |   6
white  |   1


flavor    | occurences
----------------------
lemon     |   4
strawberry|   1
mango     |   2
chocolate |   2

Uhmmm what about too if I have a predefined list of colors and flavors to pick from, so that colors/flavors that don't occur on the data table get a 0 count?

colors | occurrences
--------------------
green  |   4
blue   |   2
red    |   6
white  |   1
black  |   0


flavor    | occurences
----------------------
lemon     |   4
strawberry|   1
mango     |   2
chocolate |   2
cherry    |   0

So, what would be the SQL queries to retrieve those?

Upvotes: 1

Views: 2619

Answers (1)

Laurence
Laurence

Reputation: 10976

To do all the colors in the ColorsFlavors table

Select
  cf.Color,
  Count(*)
From
  ColorsFlavors cf
Group By
  cf.Color

If you have a predefined list in a table (which I'll call Colors), and you want to include zeros:

Select
  c.Color,
  Coalesce(Count(*), 0)
From
  Colors c
    Left Outer Join
  ColorsFlavors cf
    On c.Color = cf.Color
Group By
  c.Color

If you have a predefined list that somebody typed in

Select
  c.Color,
  Coalesce(Count(*), 0)
From  (
    Select 'green' As Color Union All
    Select 'blue' Union All
    Select 'red' Union All
    Select 'white' Union All
    Select 'black'
  ) c
    Left Outer Join
  ColorsFlavors cf
    On c.Color = cf.Color
Group By
    c.Color

With this you should be able to work out flavours!

Upvotes: 4

Related Questions