Something basic here : Invalid Group function

Question

Fairly basic ; Invalid group function. i know i need to be using HAVING but not sure how to rewrite it.

update deals d 
join deal_products dp on dp.deal_id = d.id
set d.products_count = count(distinct product_id)
where dp.active_flag=1 and dp.enabled_flag=1 and d.status != 'deleted';

Answer 1

You can't use an aggregation function like count without a group by and assign this value as a result
In this case you should use a proper subselect for obtain the value to assign at d.products_count

update deals d 
join deal_products dp on dp.deal_id = d.id
set d.products_count = (select count(distinct product_id) 
                         from  your_table  where Your_condition)
where dp.active_flag=1 and dp.enabled_flag=1 and d.status != 'deleted';