-1

Have the need to run a bit more complex of a MySQL query. I have two tables that I need to join where one contains the primary key on the other. That's easy enough, but then I need to find the number of occurrences of each ID returned as well, and ultimately sort all the results by this number.

Normally this would just be a group by, but I also need to see ALL of the results (so if it were a group by containing 10 records, I'd need to see all 10, as well as that count returned as well).

So for instance, two tables could be:

Customers table:

CustomerID  name address phone  etc..

Orders table:

OrderID  CustomerID  product info etc..

The idea is to output, and sort the orders table to find the customer with the most orders in a given time period. The resultant report would have a few hundred customers, along with their order info below.

I couldn't figure out a way to have it return the rows containing ALL the info from both tables, plus the number of occurences of each in one row. (customer info, individual orders info, and count).

I considered separating it into multiple queries (get the list of top customers), then a bunch of sub-queries for each order programmatically. But that was going to end up with many hundreds of sub-queries every time this is submitted.

So I was hoping someone might know of an easier way to do this. My thought was to have a return result with repeated information, but get it only in one query.

Thanks in advance!

Shadow
  • 33,525
  • 10
  • 51
  • 64
Bryant
  • 454
  • 4
  • 20
  • Please show what you have tried, including the queries that are "not working" – nomistic Jul 27 '15 at 17:26
  • Only things I've tried were the separate queries I mentioned. Not even sure how to accomplish what I'm trying to do. So essentially a join between table A, and B on a certain column, but then sorted by the number of occurences of that ID in table B. – Bryant Jul 27 '15 at 17:28
  • Write an inline view to give you the count of the number of orders within the time period returning the customerID and count. mySQL doesn't support windowed sets (analytic `over`) otherwise you could do this without the subquery. Join this back to customer table for your count. Or... as this is mySQL, use a user defined variable to give you a running total such as http://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql – xQbert Jul 27 '15 at 17:37

1 Answers1

4
SELECT CUST.CustomerID, CUST.Name, ORDR.OrderID, ORDR.OrderDate, ORDR.ProductInfo, COUNTS.cnt
  FROM Customers CUST
       INNER JOIN Orders ORDR
          ON ORDR.CustomerID = CUST.CustomerID
        INNER JOIN 
       (
          SELECT C.CustomerID, COUNT(DISTINCT O.OrderID) AS cnt
            FROM Customers C
                 INNER JOIN Orders O 
                    ON O.CustomerID = C.CustomerID
                 GROUP BY C.CustomerID
       )  COUNTS
          ON COUNTS.CustomerID = CUST.CustomerID
       ORDER BY COUNTS.cnt DESC, CustomerID

This will return one row per order, displayed by customer, ordered by the number of orders for that customer.

Curt
  • 5,518
  • 1
  • 21
  • 35
  • Absolutely perfect, I know it's unorthodox but was exactly what I needed for what I'm doing. Thanks so much! – Bryant Jul 28 '15 at 15:12