SQL计数出现行属于的某些类别 [英] 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

我希望进行SQL查询（或查询？），每种颜色的行数，以及每种颜色的总行数。

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.

类似这样：

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

那么，什么是SQL查询来检索这些？

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

推荐答案

在ColorsFlavors表中执行所有颜色

To do all the colors in the ColorsFlavors table

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

如果您在表格（我称为Colors）中有一个预定义列表，并且要包含零：

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

$ b b

有了这个，你应该能够做出口味！

With this you should be able to work out flavours!

这篇关于SQL计数出现行属于的某些类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！