Ayuda, consulta SQL

[Deemo]

Buenas, tengo un problema con una consulta SQL que no logro realizar, a ver si algún alma caritativa puede ayudarme. El planteamiento es simple:

Tenemos la siguiente tabla (lo haré tomando un ejemplo simple):

tabla:

Pinturas_de_colores

ID | Color | Estado
-----------------------------------
1 | Rojo | En uso
2 | Verde | En uso
3 | Azul | En uso
4 | Rojo | En reserva
5 | Rojo | En reserva
6 | Verde | En reserva
7 | Azul | Terminado

Quiero crear una tabla que presente una columna adicional,
que sólo para aquellas pinturas en uso,
presente el número de pinturas del mismo color que quedan en reserva,
es decir:

resultado consulta SQL:

Color | Estado | En reserva
----------------------------------------------------------
Rojo | En uso | 2
Verde | En uso | 1
Azul | En uso | 0
Rojo | En reserva |
Rojo | En reserva |
Verde | En reserva |
Azul | Terminado |


El objetivo, en este sencillo ejemplo, es poder ordenar una serie de artículos que están en uso al principio de la tabla y de un vistazo ver de los mismos cuántos quedan de reserva, sin tener que examinar el resto de la tabla.

Gracias por vuestro consejo

[iislas]

¿De donde sale el dato EN RESERVA?

[flaviovich]

Quedaria asi:

Código:

SELECT p.id,p.Color,p.Estado,count(t.ID)[En reserva]
FROM Pinturas_de_colores P
LEFT JOIN Pinturas_de_colores T ON P.Color=T.Color AND P.Estado='En uso' AND T.Estado='En reserva'
GROUP BY p.id,p.Color,p.Estado
order by p.id