will be helpful for those in need There are a few things to note. The "SELECT" fields can not contain function calls, but starting with version 2.0.2, DreamFactory supports virtual fields. Create vcol2 to be "HOUR(col2)" and vcol1 to be "MAX(col1)-MIN(col1)" then use them in your field parameter. As for the filter error, there is a bug in the parsing that is fixed in the 2.0.3 build coming out next week (12/21), but all function calls must be surrounded by parens, i.e. "col3 = (CURDATE())".
this will help I tried referring to other questions; I've inferred that sub-queries cannot be used on aggregate functions, but I cannot solve this use case.
c.name as customer_name,
sum(coalesce(p.price, 0) * coalesce(pr.quantity, 0)) as amount_purchased
left join PurchaseRecords pr on c.c_id = pr.c_id
left join ModelInfo mi on mi.m_id = pr.m_id
left join ProductInfo p on p.p_id = mi.p_id
MySQL - Index to speed up queries with aggregate functions