Re: FN-FORUM: Help/guidance with a bit of tricky php/MySQL

date posted 4th March 2008 12:56

On Tue, 2008-03-04 at 14:04 +0000, Duncan Glendinning wrote:
> Morning Joe,
>
> Thanks for that, worked a treat, except I had to change it to
>
> select productid, variant, sum(quantity) AS quantity_alias from Order_items
> group by productid, variant
> order by quantity_alias desc;
>
> I was wondering if you or someone else could help. There is an extra
> layer of complication I need to add to this, then I am done:
> Each Order_Items entry is assigned to a specific Order by orderid.
> What I need to do is only be counting the quantities in Order_Items of
> products and variants which form part of an order where the
> orderstatus in the Order table is set to "Completed".
>
> So:
>

You were nearly there, I think. Try:

SELECT productid, variant, sum(quantity) AS quantity_alias from Order_items
WHERE orderstatus IN ( select Orders_id from orders where status = 'Paid' )
group by productid, variant
order by quantity_alias desc;

(If this is mysql you need a 4.x/5.x version for the subselect)

Graham



>
> Any ideas?
>
> Thanks for your help by the way, and sorry for bugging you!!!
> Cheers,
>
> Duncan
>