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

A final note on the full solution is that it reduced my original query time from approx 4 secs down to 0.08 secs — ticallian
– ticallian, Commented Nov 19, 2009 at 6:13

Dave Barker · Accepted Answer · 2009-11-19 04:27:08Z

36

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

answered Nov 19, 2009 at 4:27

Dave Barker

6,4972 gold badges27 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Julian · Accepted Answer · 2014-01-04 16:15:48Z

12

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`

edited Jan 4, 2014 at 16:15

answered Dec 19, 2013 at 11:00

Julian

1211 silver badge3 bronze badges

3 Comments

phil_w Over a year ago

Thank you. For those wondering, I understand the idea is to do one inline select CONCATenating all you need, then extract separate columns from this, using variables. I think in some cases this is more efficient because subqueries are not always well optimized...

xmedeko Over a year ago

Wicked! Nope @phil_w, the idea is a fake, not used concat to exploit it of the variable setting.

xmedeko Over a year ago

@Julian the first assignments to null are not necessary, I think. Also, using case ... when instead of concat may be better for large strings.

OMG Ponies · Accepted Answer · 2009-11-19 05:09:37Z

10

Give this a shot:

  SELECT CONCAT(o.order_month, ' ', o.order_year),
         o.total_orders,
         o.total_amount,
         x.sum_order_total,
         x.ordercustomerfk,
         x.customername
    FROM (SELECT MONTH(t.orderdate) AS order_month,
                 YEAR(t.orderdate) AS order_year
                 COUNT(t.orderid) AS total_orders, 
                 SUM(t.ordertotal) AS total_amount
            FROM ORDERS t
        GROUP BY MONTH(t.orderdate), YEAR(t.orderdate)) o
    JOIN (SELECT MONTH(t.orderdate) AS ordermonth,
                 YEAR(t.orderdate) AS orderyear
                 SUM(t.ordertotal) 'sum_order_total',
                 t.ordercustomerfk,
                 c.customername
            FROM ORDERS t
            JOIN CUSTOMERS c ON c.customerid = o.ordercustomerfk
        GROUP BY t.ordercustomerfk, MONTH(t.orderdate), YEAR(t.orderdate)) x ON x.order_month = o.order_month
                                                                            AND x.order_year = o.order_year
ORDER BY o.order_year DESC, o.order_month DESC

answered Nov 19, 2009 at 5:09

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

1 Comment

ticallian Over a year ago

Sorry, didn't notice your answer until I posted a revised version of Dave Barkers solution. However, it looks along the same lines as what finally worked for me so thanks.

Collectives™ on Stack Overflow

MySQL: Returning multiple columns from an in-line subquery

FULL ANSWER

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

FULL ANSWER

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related