SQL Group By - 不同的颜色

Question

我是 SQL 新手。我希望你能帮助我。

我需要显示每个对象的数量以及其中有多少具有红色和蓝色。 (如下表)

Objectname | totalQuantity | Quantity red | Quantity blue
----------------------------------------------------------
Object A   |        22     |      10      |      12 
Object B   |        11     |       9      |      2
Object C   |        14     |       5      |      9

例如，有 22 个对象 A。10 个对象 A 的颜色为红色，另外 12 个对象 A 的颜色为蓝色。

我有以下 SQL 代码:

SELECT count(object_id) AS totalQuantity
FROM mytable
WHERE projectname='ProjectOne' AND projectleader='Mr.Smith'
GROUP BY Objectname

神话:

Object_id | Objectname | color  | projectname | projectleader
-------------------------------------------------------------
837283    | Object C   |  red   | ProjectOne  | Mr.Smith
836432    | Object A   |  blue  | ProjectOne  | Mr.Smith
839898    | Object A   |  blue  | ProjectOne  | Mr.Smith
839873    | Object A   |  red   | ProjectOne  | Mr.Smith
835652    | Object B   |  red   | ProjectOne  | Mr.Smith
.         |    .       |   .    |       .     |     .
.         |    .       |   .    |       .     |     . 
.         |    .       |   .    |       .     |     .

它仅显示 totalQuantity。
我如何显示数量红色和数量蓝色？

Answer 1

像这样的事情可能会奏效:

SELECT "Objectname",
  COUNT("Object_id") AS totalQuantity,
  SUM(CASE 
      WHEN "color" = 'red'
        THEN 1
      ELSE 0
      END) AS QuantityRed,
  SUM(CASE 
      WHEN "color" = 'blue'
        THEN 1
      ELSE 0
      END) AS QuantityBlue
FROM "Table1"
WHERE "projectname" = 'ProjectOne'
  AND "projectleader" = 'Mr.Smith'
GROUP BY "Objectname";

sqlfiddle demo