Query Problem

[rmorgan]

I have been looking at code entirely to long and I keep getting a sql syntax error with the below code. Just wondering if anyone can see anything right off with the query before I go into more detail, I think I need some fresh eyes looking at it. This is being run against a MySQL server. TIA

select bcastreq.bcast_id, bcastreq.affiliate_id, bcastreq.subject, DATE_FORMAT(bcastreq.reqtimestamp, '%m/%d/%Y') as reqtimestamp, affiliate.affiliatename,  bcastreq.affiliatetype_id, count(editbcastreq.bcast_id) as editcount, editbcastreq.affiliatetype_id
from bcastreq
left join editbcastreq
on bcastreq.bcast_id = editbcastreq.bcast_id
left join affiliate
on bcastreq.affiliate_id = affiliate.affiliate_id
group by bcastreq.bcast_id
<cfif Arguments.filtercolumn NEQ "" AND Arguments.filter NEQ "">
where #Arguments.filtercolumn# like '#Arguments.filter#%'
</cfif>
<cfif Arguments.gridsortcolumn NEQ "">
order by #Arguments.gridsortcolumn# #Arguments.gridsortdirection#
</cfif>

[ilssac]

I'm not sure, can the group by clause come before the where clause like that.  IIRC there is a certain expected order to the SQL clauses.  I'm just not sure if this is required or just convention.

[Dan_Bracuk]

Ian's observation is correct.  The where clause must precede the group by clause.

Also.

You are selecting 3 or 4 fields but only grouping by 1.

Is date_format a valid mysql function?

[rmorgan]

Thanks all. The date_format has been working fine. I did not start  to have problems until I added the cfif statements to the query. I will try and give your suggestions a try today if I get a chance. Thanks again.

[rmorgan]

Thanks. Its always something simple. The location of the where clause was the problem. Much appreciated.