Subquery – define select list. The subquery returns the same value for each result line

do you know what I have done wrong? My subquery is returining the same value for all lines.

SELECT distinct
ca.descripcion AS 'Storage_Condition',
--Warehouses.[Nombre] as [Warehouse],
COUNT(pos.rack) as [Max Capacity],

(select distinct 
count(pos.rack) + ''
FROM Depositos de
INNER JOIN Posiciones pos ON  de.deposito_id = pos.deposito_id
INNER JOIN Condiciones_Almacenamiento ca ON  de.condicion_almac_id = ca.condicion_id
INNER JOIN Estados_Posicion ep ON  pos.estado_posicion_id = ep.estado_posicion_id 
INNER JOIN Tipo_posiciones tipopos ON tipopos.tipo_posicion_id = pos.tipo_posicion_id 
INNER JOIN Warehouses ON   Warehouses.[warehouse_id] = de.[warehouse_id]
WHERE ((CASE WHEN 1 = 2 THEN ep.descripcion_es ELSE ep.descripcion END) = 'Occupied') AND tipopos.descripcion = 'Shelf' AND Warehouses.nombre like ('%Frankfurt%')) as [Used Capacity]

FROM  Depositos de

INNER JOIN Posiciones pos ON  de.deposito_id = pos.deposito_id
-- AND  de.warehouse_id = @warehouse_id
INNER JOIN Condiciones_Almacenamiento ca ON  de.condicion_almac_id = ca.condicion_id
INNER JOIN Tipos_Subproyecto ts ON  de.tipo_subproyecto_id = ts.tipo_subproyecto_id
INNER JOIN Estados_Posicion ep ON  pos.estado_posicion_id = ep.estado_posicion_id
INNER JOIN Tipo_posiciones tipopos ON tipopos.tipo_posicion_id = pos.tipo_posicion_id 
INNER JOIN Warehouses ON   Warehouses.[warehouse_id] = de.[warehouse_id]
where 
--ca.descripcion = '15-25.C'
tipopos.descripcion = 'Shelf'
and Warehouses.nombre like ('%Frankfurt%')
--and (CASE WHEN 1 = 2 THEN ep.descripcion_es ELSE ep.descripcion END) = 'Occupied'

Group by 
--Warehouses.[Nombre] 
ca.descripcion
Order by COUNT(pos.rack) desc

Thanks a lot in advance for your any advice! 🙂

Answer

I think you just want conditional aggregation:

SELECT ca.descripcion AS Storage_Condition,
       count(pos.rack) as [Max Capacity],
       sum(case when ep.descripcion = 'Occupied' then 1 else 0 end) as used_capacity
FROM  Depositos de JOIN
      Posiciones pos
      ON  de.deposito_id = pos.deposito_id JOIN
      Condiciones_Almacenamiento ca
      ON de.condicion_almac_id = ca.condicion_id JOIN
      Tipos_Subproyecto ts
      ON  de.tipo_subproyecto_id = ts.tipo_subproyecto_id JOIN
      Estados_Posicion ep
      ON  pos.estado_posicion_id = ep.estado_posicion_id JOIN
      Tipo_posiciones tipopos
      ON tipopos.tipo_posicion_id = pos.tipo_posicion_id JOIN
      Warehouses
      ON  Warehouses.[warehouse_id] = de.[warehouse_id]
WHERE tipopos.descripcion = 'Shelf' and
      Warehouses.nombre like '%Frankfurt%'
GROUP BY ca.descripcion
ORDER BY COUNT(pos.rack) desc;