I think Shawn nailed it with
SELECT, p.prod_name, 
  SUM(IF( IS NULL,0,oi.quantity)) AS Qty
FROM products AS p
INNER JOIN order_items AS oi ON ( = oi.product_id)
INNER JOIN orders as o ON (oi.order_id =
  AND o.status NOT IN ('cancelled', 'pending', 'ghost')
  AND o.created BETWEEN "2006-02-23 00:00:00" AND "2006-02-23 23:59:59"
GROUP BY, p.prod_name
(you can often speed up a join by moving conditions from the Where clause to the unnamed side of an Inner or Left Join).



Scott Haneda wrote:
Is this what you mean?

  count(oi.product_id) AS mycount
INNER JOIN products ON
LEFT JOIN order_items AS oi ON ( = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
GROUP BY oi.product_id
ORDER BY mycount;

Well, sort of, here is what I managed to coble together, which gets me
pretty close, it is just what I want, other than it is missing products with
a zero count.  This tells me those products have not been ordered ever, but
I would like to know what they are.

SELECT, oi.prod_name, sum(oi.quantity) as qty
FROM orders as o
INNER JOIN order_items as oi
ON ( = oi.order_id)
LEFT JOIN products as p
ON ( = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
(o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59")
GROUP BY oi.product_id
ORDER by qty ASC