SQL statement issue

OK, i might be completely wrong now, but try separating them within the same script, but giving them individual identities.
 
Then at the end of the query, join the 2 parts together using the join you want to.
 
So in essence you have 3 individual queries within the same script.
 
 
Where I think you're going wrong is trying to include the
 
(SELECT orderid, op.ordprodname, op.ordprodid, SUM(op.ordprodqty) AS qty FROM isc_orders o JOIN isc_order_products op on op.orderprodid=o.orderid WHERE orddate BETWEEN 1271853079 AND 1272637707 GROUP BY op.ordprodid) AS qty,
 
part as a sub query.

Yep, defo that bit as a sub query that is cocking it up! Can't figure out how to join them as seperate ones though...
 
Bah, will leave for a bit, other stuff to do!!
 
Ta for input so far

No worries man. Give us an update when you've sorted it, would be interested to see the solution.

lol, you might be waiting a while..!!

Definately go for a JOIN rather than subquery. You'll get a much better query plan too.
 
What does the estimated query plan show? If it's SQL 2008 R2 you may get a useful query optimsation tip...

(SELECT orderid, op.ordprodname, op.ordprodid, SUM(op.ordprodqty) AS qty FROM isc_orders o JOIN isc_order_products op on op.orderprodid=o.orderid WHERE orddate BETWEEN 1271853079 AND 1272637707 GROUP BY op.ordprodid) AS qty
 
Shouldn't this have all non aggregate fields in the group clause:-
 
group by orderid,op.ordprodname, op.ordprodid 
 
ie
  (SELECT orderid, op.ordprodname, op.ordprodid, SUM(op.ordprodqty) AS qty FROM isc_orders o JOIN isc_order_products op on op.orderprodid=o.orderid WHERE orddate BETWEEN 1271853079 AND 1272637707 group by orderid,op.ordprodname, op.ordprodid) AS qty