ERROR: la subconsulta usa la columna «o.created» no agrupada de una consulta exterior
LINE 1: ...ate_part('month',ord.created) = date_part('month',o.created)...
Necesito que la consulta externa vaya usando lo que me retornan desde afuera las columnas
date_part('year',o.created) ,
date_part('month',o.created) , las cuales segun la fecha me van retornando el numero del mes 1..2 etc, si alguien pudiera indicarme donde se encuentra el error le agradeceria.
Código:
Saludos. SELECT
max(pj.c_project_id),
pj.name,
date_part('year',o.created) AS anio,
date_part('month',o.created) AS mes,
(
SELECT
COUNT(ol.c_orderline_id)
FROM c_orderline ol
INNER JOIN c_order ord ON (ord.c_order_id = ol.c_order_id)
INNER JOIN c_project prj ON (ord.c_project_id = prj.c_project_id)
INNER JOIN m_product pr ON (pr.m_product_id = ol.m_product_id)
INNER JOIN m_product_category pc ON (pr.m_product_category_id = pc.m_product_category_id)
WHERE ord.issotrx = 'Y'
AND ord.docstatus = 'CO'
AND ord.isactive = 'Y'
AND ord.c_project_id = 1000035
AND pc.m_product_category_id = 1000304
AND date_part('month',ord.created) = date_part('month',o.created)
AND date_part('year',ord.created) = date_part('year',o.created)
) AS num_aptos
FROM c_project pj
INNER JOIN c_order o ON (o.c_project_id = pj.c_project_id)
WHERE o.created BETWEEN '2012-01-01' AND '2012-02-09'
AND pj.c_project_id = 1000035
GROUP BY
date_part('year',o.created),
date_part('month',o.created),
pj.c_project_id,
pj.name
ORDER BY date_part('year',o.created),
date_part('month',o.created)


