时间戳

首页 > 解决方案 > 按有收入的订单数量分组的客户数量

mysql - 按有收入的订单数量分组的客户数量

问题描述

我正在寻找一个查询，它将为我提供按制造数量分组的订单数量以及我想要这些数字的收入。

举个例子。

| Number of Orders  | Numbers of Customers  | Revenue Of Orders  |
| 1                 | 312                   | 4350.88            |
| 2                 | 208                   | 3490.00            |
| 3                 | 152                   | 2240.50            |

我的前两列工作正常。这就是那个查询

SELECT
    r.num_of_orders ,
    count(*) AS num_of_customers
FROM
    (
        SELECT
            count(*) AS num_of_orders
        FROM
            reservations r
        WHERE
            created_at >= '2008-01-01 00:00:00'
        AND `status` = 'closed'
        GROUP BY
            r.customer_id
    ) r
GROUP BY
    r.num_of_orders

我尝试增加收入。

SELECT
    r.num_of_orders ,
    count(*) AS num_of_customers,
    sum(b.total) as total_revenue
FROM
    (
        SELECT
            count(*) AS num_of_orders
        FROM
            reservations r
        WHERE
            created_at >= '2008-01-01 00:00:00'
        GROUP BY
            r.customer_id
    ) r,
    (
        SELECT
            sum(payments.total) AS total
        FROM
            reservations r
        JOIN payments ON payments.id = r.reservation_id
        WHERE
            r.created_at >= '2008-01-01 00:00:00'
        GROUP BY
            r.customer_id
    ) b
GROUP BY
    r.num_of_orders

但我知道这些收入数字已经出来了。

希望你能给点建议。

标签： mysqlsql

解决方案

将其添加到原始子查询中：

SELECT r.num_of_orders, count(*) AS num_of_customers,
       SUM(revenue) as total_revenue
FROM (SELECT COUNT(DISTINCT r.reservation_id) AS num_of_orders,
             SUM(p.total) as revenue
      FROM reservations r JOIN
           payments p
           ON p.id = r.reservation_id
      WHERE r.created_at >= '2008-01-01' AND
            r.status = 'closed'
      GROUP BY r.customer_id
     ) r
GROUP BY r.num_of_orders;

推荐阅读