Hola gustavowd:
Lo que pretendes hacer si es posible, y en muchas ocasiones hemos tratado en este foro distintas maneras para poder hacerlo, para la próxima sería conveniente que utilizaras las opciones de búsqueda del foro para ver si ya existe algún problema similar, y sólo si no encuentras nada que te sea de ayuda entonces sí hacer una nueva publicación. Ojo con eso.
Ahora bien, entrando en materia, si sólo tienes tres estados en tu tabla, y estos no van a incrementarse, la manera más sencilla que tienes es hacer un sum condicional:
Código SQL:
Ver originalDECLARE @Tabla TABLE (Producto VARCHAR(15), Estado INT)
INSERT INTO @Tabla VALUES ('Item1', 1)
INSERT INTO @Tabla VALUES ('Item2', 2)
INSERT INTO @Tabla VALUES ('Item3', 1)
INSERT INTO @Tabla VALUES ('Item4', 3)
INSERT INTO @Tabla VALUES ('Item5', 1)
INSERT INTO @Tabla VALUES ('Item6', 2)
INSERT INTO @Tabla VALUES ('Item7', 2)
INSERT INTO @Tabla VALUES ('Item8', 3)
SELECT * FROM @Tabla
SELECT
COUNT(*) AS Productos,
SUM(CASE WHEN Estado = 1 THEN 1 ELSE 0 END) Estado1,
SUM(CASE WHEN Estado = 2 THEN 1 ELSE 0 END) Estado2,
SUM(CASE WHEN Estado = 3 THEN 1 ELSE 0 END) Estado3
FROM @Tabla
Si por el contrario tus estados son más de tres o es un número variable, quizás te convenga utilizar tablas pivote (PIVOT).
Saludos
Leo.