0

This is what I have:

customer
--------
customerId INT(10)
name TINYTEXT
phone VARCHAR(10)
...

order
-----
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 GROUP_CONCAT(`count` SEPARATOR ',') AS `countCsv`
FROM (
    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 GROUP_CONCAT(`month` SEPARATOR ',') AS `monthCsv`
FROM (
    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.

0

Step 1: Get the data without the complexity:

CREATE TEMPORARY TABLE t
SELECT cus.customerId,
       cus.name,
       CONCAT(cus.LEFT(dateCompleted, 7), '-01') AS month_of,
       o.orderId
    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,
       a.name,
       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 '19 at 1:06
  • @pangeam - Then you need to LEFT JOIN from a table that has all months. – Rick James Jan 3 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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