Incorrect Select syntax

OK, got it in CR SQL Expression field

SELECT "ORDER_DETAIL"."SUBSYSTEM",
Count("ORDER_DETAIL"."PRODUCT_ID")
from "ORDER_DETAIL"
where "ORDER_DETAIL"."SUBSYSTEM"='SUB' or "ORDER_DETAIL"."SUBSYSTEM"='MBR'
group by "ORDER_DETAIL"."PARENT_PRODUCT"


but will not go through...'incorrect syntax near word Select'.'.That's because your aggregate function, Count(PRODUCT_ID), can't be different than your group by clause PARENT_PRODUCT.

Try putting PARENT_PRODUCT in your select statement and you may need to add SUBSYSTEM to your group by too, depending on your database backend.

select subsystem, parent_product, count(product_id)
from order_detail
where order_detail.subsystem in ('SUB', 'MBR')
group by subsystem, parent_product

or

select parent_product, count(product_id)
from order_detail
where order_detail subsystem in ('SUB', 'MBR')
group by parent_product|||Your formula keep giving me the same message.
I was trying to have formula
IIF({ORDER_DETAIL.SUBSYSTEM}='SUB' or {ORDER_DETAIL.SUBSYSTEM}='MBR',Count({ORDER_DETAIL.PRODUCT_ID}),0)
which gives me what I want but counts total of Product_IDs and not distributes it by Produci_ID.

In ideal it shoul be result of the formula grouped by Product_ID.

I am affraid I will need a subreport.

Thanks for your help.