Error => #1242 – Subquery returns more than 1 row

I am selecting order data from my orders table with that I also need the order product’s count which contains a specific word

Account TopUp 200,
Account TopUp 500,
Account TopUp 1000,

The order contain multiple product means one order contains both(Account TopUp 200, Account TopUp 500) and other products

I made a subquery for this

            (
                    SELECT COUNT(orderprodid)
                    FROM order_products
                    WHERE orderorderid=orderid AND ordprodname like '%Account TopUp%'
                ) AS  orderproductcount

But it gives Error: #1242 – Subquery returns more than 1 row

The full query is :

SELECT o.*,
                    (
                        SELECT COUNT(messageid)
                        FROM order_messages
                        WHERE messageorderid=orderid
                    ) AS nummessages,
                    (
                        SELECT COUNT(messageid)
                        FROM order_messages
                        WHERE messageorderid=orderid AND messagestatus != 'read'
                    ) AS numunreadmessages,
                    (
                        SELECT COUNT(messageid)
                        FROM order_messages
                        WHERE messageorderid=orderid AND messagefrom='customer' AND messagestatus='unread'
                    ) AS newmessages,
                    (
                        SELECT orderproapi
                        FROM order_products
                        WHERE orderorderid=orderid
                    ) AS  orderproapi,
                    (
                        SELECT COUNT(orderprodid)
                        FROM order_products
                        WHERE orderorderid=orderid AND ordprodname like '%Account TopUp%'
                    ) AS  orderproductcount
                FROM orders o
                LEFT JOIN customers c ON (o.ordcustid=c.customerid)
                LEFT JOIN order_status s ON (s.statusid=o.ordstatus)

Answer

The subquery you highlighted in your question actually doesn’t have any problem with it and won’t cause the error message you are seeing. But this subquery will:

(
    SELECT orderproapi
    FROM order_products
    WHERE orderorderid = orderid
) AS orderproapi,

This subquery will most likely return multiple records/values, and therefore makes no sense appearing in the SELECT clause. Fix this problem and your query should work.