This is what I have:

customerId INT(10)
phone VARCHAR(10)

orderId int(10)
customerId INT(10)
dateCompleted DATETIME

This is what I need:

custId  name  thisMo  lastMo  last3Mos  monthsThisYear              ordersPerMonth
------  ----  ------  ------  --------  --------------              --------------
1       John  5       10        28      2019-01,2019-02,2019-03,..  7,11,9..
2       Bill  9       15        33      2019-01,2019-02,2019-03,..  12,10,14..
3       Jack  3       8         19      2019-01,2019-02,2019-03,..  3,7,5..

Trying to generate a table showing customers, number of orders they've placed this month, last month, and last 3 months, along with a spark line chart showing number of orders per month for the entire past year (hence the comma separated lists of months and orders per month)

Currently, they're using a single query to get all customers, then each customer is looped through to generate the table and additional queries are made to get the numbers. The queries to get the csv are as follows:

    SELECT SUM(IF(`customerId` = {$row['customerId']}, 1, 0)) AS `count`
    FROM `order`
    WHERE DATE(`dateCompleted`) BETWEEN DATE('{$sqlStartDate}') AND DATE('{$sqlEndDate}')
    GROUP BY DATE_FORMAT(`dateCompleted`, '%Y-%m')
) `Order`

    SELECT DATE_FORMAT(`dateCompleted`, '%Y-%m') AS `month`
    FROM `order`
    WHERE DATE(`dateCompleted`) BETWEEN DATE('{$sqlStartDate}') AND DATE('{$sqlEndDate}')
    GROUP BY DATE_FORMAT(`dateCompleted`, '%Y-%m')
) `Order`

...but this is resulting in 5 extra queries per row per page load and it's taking forever. I've been trying all day (8-1/2 hours now) to search and extrapolate from similar questions and I just can't get anything even close to working in a single query.

Edit - added customerId to order table to show customerId as the relationship between the two tables.

1 Answer 1


Step 1: Get the data without the complexity:

SELECT cus.customerId,
       CONCAT(cus.LEFT(dateCompleted, 7), '-01') AS month_of,
    FROM Customer AS cus
    JOIN orders AS o  ON ??? -- how do the table relate??

(Fox and try that query now.)

Step 2: Compute the counts:

SELECT customerId, COUNT(*) AS ct
    FROM t
    WHERE dateCompleted
    GROUP BY customerId, month_of

(Try that query now.)

Step 3: Use that as a "derived" table to build the ultimate table.

SELECT a.customerId AS custId,
       SUM(a.month_of >= CURDATE() - INTERVAL 1 MONTH) AS thisMo,
       SUM(a.month_of >= CURDATE() - INTERVAL 2 MONTH AND
           a.month_of  < CURDATE() - INTERVAL 2 MONTH) AS lastMo,
       SUM(a.month_of >= CURDATE() - INTERVAL 3 MONTH) AS last3mo,
       GROUP_CONCAT( ??? )  as monthsThisYear,  -- unclear where this comes from
       GROUP_CONCAT(t.ct ORDER BY month_of) AS ordersPerMonth
    FROM ( ..query 1, above.. ) AS a
    JOIN ( ..query 2, above.. ) AS cts  USING(customerId)
    GROUP BY customerId, a.name

There are several holes, but maybe this gives you some useful clues.

If you are using MySQL 8.0 or MariaDB 10.2, use WITH instead of a temp table.

  • "GROUP_CONCAT( ??? ) as monthsThisYear, -- unclear where this comes from" - because if there are no orders for a given month, selecting all orders and grouping by month excludes that month from the resulting dataset. Using GROUP_CONCAT as shown in my example would, for someone who only orders every other month, for example, "2019-01,2019-03,2019-05,2019-07,2019-09,2019-11" for months and "n,n,n,n,n,n" for number of orders per month (where n = number of orders)
    – pangeam
    Dec 24, 2019 at 1:06
  • @pangeam - Then you need to LEFT JOIN from a table that has all months.
    – Rick James
    Jan 3, 2020 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.