SQL Group By - different colors

Question

I am new to SQL. I hope you can help me.

I need to show the quantity of every object and how many of these have the color red and the color blue. (like the table below)

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

So for example there are 22 Object A. 10 Objects A have the color red and the other 12 Object A have the color blue.

I have the following SQL code:

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

mytable:

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
.         |    .       |   .    |       .     |     .
.         |    .       |   .    |       .     |     . 
.         |    .       |   .    |       .     |     .

It shows only the totalQuantity.
How do i display Quantity red and Quantity blue?

Answer 1

Something like this might work:

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