SQL statement issue

On the off chance anyone on here is good at pulling apart SQL queries, I can't get this one to work:
 
SELECT p.*,
FLOOR(prodratingtotal / prodnumratings) AS prodavgrating, pi.*,
SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', 2), ' ', 1) / SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', -1), ' ', 2) as ratio,
(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,
(SELECT discountpercent FROM isc_customer_group_discounts disc WHERE disc.discounttype='PRODUCT' AND disc.customergroupid='9' AND disc.catorprodid=p.productid) AS prodgroupdiscount,
(SELECT discountmethod FROM isc_customer_group_discounts disc WHERE disc.discounttype='PRODUCT' AND disc.customergroupid='9' AND disc.catorprodid=p.productid) AS discountmethod
FROM (SELECT DISTINCT ca.productid, FLOOR(prodratingtotal / prodnumratings) AS prodavgrating
FROM isc_categoryassociations ca
INNER JOIN isc_products p ON p.productid = ca.productid WHERE p.prodvisible = 1 AND p.prodbrandid = 51) AS ca
INNER JOIN isc_products p ON p.productid = ca.productid
LEFT JOIN isc_product_images pi ON (pi.imageisthumb = 1 AND p.productid = pi.imageprodid)
ORDER BY p.prodcode ASC, p.prodcode ASC
 
I know that it works fine like this:
SELECT p.*,
FLOOR(prodratingtotal / prodnumratings) AS prodavgrating, pi.*,
SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', 2), ' ', 1) / SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', -1), ' ', 2) as ratio,
(SELECT discountpercent FROM isc_customer_group_discounts disc WHERE disc.discounttype='PRODUCT' AND disc.customergroupid='9' AND disc.catorprodid=p.productid) AS prodgroupdiscount,
(SELECT discountmethod FROM isc_customer_group_discounts disc WHERE disc.discounttype='PRODUCT' AND disc.customergroupid='9' AND disc.catorprodid=p.productid) AS discountmethod
FROM
(SELECT DISTINCT ca.productid, FLOOR(prodratingtotal / prodnumratings) AS prodavgrating
FROM
isc_categoryassociations ca
INNER JOIN isc_products p ON p.productid = ca.productid WHERE p.prodvisible = 1 AND p.prodbrandid = 51) AS ca
INNER JOIN isc_products p ON p.productid = ca.productid
LEFT JOIN isc_product_images pi ON (pi.imageisthumb = 1 AND p.productid = pi.imageprodid)
ORDER BY p.prodcode ASC, p.prodcode ASC
 
So I'm basically trying to get this line in there:
(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,
 
It keeps throwing

Error Code: 1241. Operand should contain 1 column(s)

 
Baffled...
 
Any ideas?

Above my paygrade. Sure there was a member on here who was a dab hand with SQL. 

Yeah, I'm a bit lost in it - don't really do much SQL, maybe an hour a month, so when I get stuck it doesn't half piss me off!!!

I googled that error, helped massively

Have you tried putting the join on a new line rather than straight after 'AS qty FROM isc_orders o'

What about the group by outside the parentheses?

No disco - don't think that alters the sytnax anyway?

Spotted I've got 'AS qty' in there twice, but that isn't the cause either

Nope, not the group by either

Doesn't it tell you which line the error is occurring on?

Nope, fails whole function

Have you tried just querying the part
 
(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
 
on its own?
 
 

Yep - though ditching the "AS qty" bit, so the 2 working queries are:
 

SELECT p.*,
FLOOR(prodratingtotal / prodnumratings) AS prodavgrating, pi.*,
SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', 2), ' ', 1) / SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', -1), ' ', 2) as ratio,
(SELECT discountpercent FROM isc_customer_group_discounts disc WHERE disc.discounttype='PRODUCT' AND disc.customergroupid='9' AND disc.catorprodid=p.productid) AS prodgroupdiscount,
(SELECT discountmethod FROM isc_customer_group_discounts disc WHERE disc.discounttype='PRODUCT' AND disc.customergroupid='9' AND disc.catorprodid=p.productid) AS discountmethod
FROM (SELECT DISTINCT ca.productid, FLOOR(prodratingtotal / prodnumratings) AS prodavgrating
FROM isc_categoryassociations ca
INNER JOIN isc_products p ON p.productid = ca.productid WHERE p.prodvisible = 1) AS ca
INNER JOIN isc_products p ON p.productid = ca.productid
LEFT JOIN isc_product_images pi ON (pi.imageisthumb = 1 AND p.productid = pi.imageprodid)
ORDER BY p.prodcode ASC, p.prodcode ASC

 
and
 

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

 
 

The 2 queries need to be joined on op.ordprodid = p.productid

I assume you've saved both the queries individually.
 
Might sound stupid, its hard to picture without the management studio in front of me, and I havent used it for the past year, but have you tried creating a third query, joining the 2 successful queries?
 
Its not ideal, but I used to do this quite a few times as a work around when trying to get various management info.

It's because you're trying to return multiple columns in a subquery (which you can't do unless it's for row comparision) and that's why it works on its own. Try gwilym's suggestion!

Disclaimer: Not used SQL for over a year now since changing jobs so I may be wrong!

Management Studio...? Erm, what's that then? I've only ever hand written SQL statements
 
Will see if I can fudge that together

Are you using iSeries or something?

SQL Server Management Studio.
 
I'm guessing you're using mySQL then?

I haven't used mySQL so not sure how to but..
 
You need to find a way to create permanent objects out of the 2 individual queries. 
 
When you've created the 2 objects, then join them in the manner you want using a simple query.

Yeah, MySQL Workbench - the SQL statements are run in PHP scripts, so I can't have anything saved anywhere, needs to be all shoved together.