MySQL: Returning multiple columns from an in-line subquery

Question

I'm creating an SQL statement that will return a month by month summary on sales.

The summary will list some simple columns for the date, total number of sales and the total value of sales.

However, in addition to these columns, i'd like to include 3 more that will list the months best customer by amount spent. For these columns, I need some kind of inline subquery that can return their ID, Name and the Amount they spent.

My current effort uses an inline SELECT statement, however, from my knowledge on how to implement these, you can only return one column and row per in-line statement.

To get around this with my scenario, I can of course create 3 separate in-line statements, however, besides this seeming impractical, it increases the query time more that necessary.

SELECT       DATE_FORMAT(OrderDate,'%M %Y') AS OrderMonth,     COUNT(OrderID) AS TotalOrders,      SUM(OrderTotal) AS TotalAmount,       (SELECT SUM(OrderTotal) FROM Orders WHERE DATE_FORMAT(OrderDate,'%M %Y') = OrderMonth GROUP BY OrderCustomerFK ORDER BY SUM(OrderTotal) DESC LIMIT 1) AS TotalCustomerAmount,      (SELECT OrderCustomerFK FROM Orders WHERE DATE_FORMAT(OrderDate,'%M %Y') = OrderMonth GROUP BY OrderCustomerFK ORDER BY SUM(OrderTotal) DESC LIMIT 1) AS CustomerID,      (SELECT CustomerName FROM Orders INNER JOIN Customers ON OrderCustomerFK = CustomerID WHERE DATE_FORMAT(OrderDate,'%M %Y') = OrderMonth GROUP BY OrderCustomerFK ORDER BY SUM(OrderTotal) DESC LIMIT 1) AS CustomerName  FROM Orders      GROUP BY DATE_FORMAT(OrderDate,'%m%y') ORDER BY DATE_FORMAT(OrderDate,'%y%m') DESC 

How can i better structure this query?

---

FULL ANSWER

After some tweaking of Dave Barkers solution, I have a final version for anyone in the future looking for help.

The solution by Dave Barker worked perfectly with the customer details, however, it made the simpler Total Sales and Total Sale Amount columns get some crazy figures.

SELECT           Y.OrderMonth,   Y.TotalOrders,  Y.TotalAmount,         Z.OrdCustFK,  Z.CustCompany,    Z.CustOrdTotal, Z.CustSalesTotal       FROM          (SELECT             OrdDate,             DATE_FORMAT(OrdDate,'%M %Y') AS OrderMonth,              COUNT(OrderID) AS TotalOrders,              SUM(OrdGrandTotal) AS TotalAmount             FROM Orders             WHERE OrdConfirmed = 1                 GROUP BY DATE_FORMAT(OrdDate,'%m%y')              ORDER BY DATE_FORMAT(OrdDate,'%Y%m') DESC)     Y INNER JOIN          (SELECT              DATE_FORMAT(OrdDate,'%M %Y') AS CustMonth,              OrdCustFK,              CustCompany,              COUNT(OrderID) AS CustOrdTotal,             SUM(OrdGrandTotal) AS CustSalesTotal          FROM Orders INNER JOIN CustomerDetails ON OrdCustFK = CustomerID         WHERE OrdConfirmed = 1         GROUP BY DATE_FORMAT(OrdDate,'%m%y'), OrdCustFK          ORDER BY SUM(OrdGrandTotal) DESC)      Z ON Z.CustMonth = Y.OrderMonth  GROUP BY DATE_FORMAT(OrdDate,'%Y%m') ORDER BY DATE_FORMAT(OrdDate,'%Y%m') DESC 

Answer 1

Move the inline SQL to be a inner join query. So you'd have something like...

SELECT  DATE_FORMAT(OrderDate,'%M %Y') AS OrderMonth, COUNT(OrderID) AS TotalOrders, SUM(OrderTotal) AS TotalAmount,  Z.OrderCustomerFK, Z.CustomerName, z.OrderTotal as CustomerTotal      FROM Orders        INNER JOIN (SELECT DATE_FORMAT(OrderDate,'%M %Y') as Mon, OrderCustomerFK, CustomerName, SUM(OrderTotal) as OrderTotal                  FROM Orders                 GROUP BY  DATE_FORMAT(OrderDate,'%M %Y'), OrderCustomerFK, CustomerName ORDER BY SUM(OrderTotal) DESC LIMIT 1) Z           ON Z.Mon = DATE_FORMAT(OrderDate,'%M %Y')     GROUP BY DATE_FORMAT(OrderDate,'%m%y'), Z.OrderCustomerFK, Z.CustomerName     ORDER BY DATE_FORMAT(OrderDate,'%y%m') DESC 

Answer 2

You can also do something like:

SELECT      a.`y`,     ( SELECT @c:=NULL ) AS `temp`,     ( SELECT @d:=NULL ) AS `temp`,     ( SELECT            CONCAT(@c:=b.`c`, @d:=b.`d`)        FROM `b`       ORDER BY b.`uid`        LIMIT 1 ) AS `temp`,     @c as c,     @d as d  FROM `a`