SELECT DAYNAME( o.date_end ) AS pd, count( * ) AS amount
FROM process op
LEFT JOIN orders o
USING ( orders_id )
WHERE op.field_name = 'product_id'
AND op.field_value = 50037
AND o.date_end IS NOT NULL
AND YEAR( o.date_end ) =2005
AND WEEK( o.date_end, 1 ) =45
GROUP BY pd
The problem is I can have more than one values with same product_id for a single 'orders_id'
I want to count only one of them not both.
SELECT DAYNAME(o.date_end) as pd,count(*) as amount
FROM (
SELECT DISTINCT(orders_id),field_name,field_value FROM process WHERE field_name = 'product_id'
) op LEFT JOIN orders o USING (orders_id)
WHERE
op.field_name = 'product_id'
AND op.field_value = 50037
AND o.date_end IS NOT NULL
AND YEAR(o.date_end) = 2005
AND WEEK(o.date_end,1) = 45
GROUP BY pd