我有两张table；一个用于客户订单，另一个用于这些订单上的套件。我试图了解从每个订单中运送的套件的百分比，但是我在生成sql查询来计算行项目（和它们的数量之和）时遇到了问题。我已经把问题简化为一组简单的数据，我很确定问题出在我的子查询中。。。但我不知道如何对这些子查询的结果应用求和，我还怀疑它们应用于整个数据集，而不是受我的联接限制：
下面是显示我的问题的完整示例（我使用quantity列作为是否应该忽略行项目的代理）。

/*DROP TABLE customer_order;
DROP TABLE customer_item;

* /

CREATE TABLE customer_order (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer varchar(255),
    PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE customer_item (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    order_id INT NOT NULL,
    quantity INT,
    part_number varchar(255),
    description varchar(255),
    shipment_date DATETIME,
    PRIMARY KEY (id),
    INDEX order_id_idx (order_id)
) ENGINE=INNODB;

INSERT INTO customer_order VALUES(1, "Spronketts LTD");
INSERT INTO customer_order VALUES(2, "Widgets Inc.");
INSERT INTO customer_order VALUES(3, "Frobizz PLC");
/*Create an entirely shipped order with some junk lines*/
INSERT INTO customer_item VALUES(NULL, 1, 1,    "01-ABC", "Spline bender",    "2018-01-01T12:00:00");
INSERT INTO customer_item VALUES(NULL, 1, 3,    "02-XYZ", "NURB reticulator", "2018-01-01T12:00:00");
INSERT INTO customer_item VALUES(NULL, 1, NULL, "",       "Junk Text",        NULL);
INSERT INTO customer_item VALUES(NULL, 1, NULL, "",       "Foobar",           NULL);
/*Create a 90% shipped order (1 item of 10 outstanding)*/
INSERT INTO customer_item VALUES(NULL, 2, 1,    "01-ABC", "Spline bender",    "2018-01-01T12:00:00");
INSERT INTO customer_item VALUES(NULL, 2, 3,    "02-XYZ", "NURB reticulator", "2018-01-01T12:00:00");
INSERT INTO customer_item VALUES(NULL, 2, 5,    "03-XYZ", "Doohickey",        "2018-01-01T12:00:00");
INSERT INTO customer_item VALUES(NULL, 2, 1,    "04-XYZ", "Whatsit",          NULL);
/*Now create a 0% shipped order*/
INSERT INTO customer_item VALUES(NULL, 3, 1,    "01-ABC", "Spline bender",    NULL);
INSERT INTO customer_item VALUES(NULL, 3, 1,    "02-XYZ", "NURB reticulator", NULL);
INSERT INTO customer_item VALUES(NULL, 3, 1,    "03-XYZ", "Doohickey",        NULL);
INSERT INTO customer_item VALUES(NULL, 3, 1,    "04-XYZ", "Whatsit",          NULL);

SELECT customer, order_id AS id,
(SELECT COUNT(*) FROM customer_item WHERE quantity IS NOT NULL) AS total_items,
(SELECT COUNT(*) FROM customer_item WHERE quantity IS NOT NULL AND shipment_date IS NOT NULL) AS shipped_items
FROM customer_item LEFT JOIN customer_order ON customer_item.order_id=customer_order.id;

/* Hoping to see:
+----------------+---+----+---+
| Spronketts LTD | 1 | 4  | 4 |
| Widgets Inc.   | 2 | 10 | 9 |
| Frobizz PLC    | 3 | 4  | 0 |
+----------------+---+----+---+
But I get
+----------------+----+-------------+---------------+
| customer       | id | total_items | shipped_items |
+----------------+----+-------------+---------------+
| Spronketts LTD |  1 |          10 |             5 |
| Spronketts LTD |  1 |          10 |             5 |
| Spronketts LTD |  1 |          10 |             5 |
| Spronketts LTD |  1 |          10 |             5 |
| Widgets Inc.   |  2 |          10 |             5 |
| Widgets Inc.   |  2 |          10 |             5 |
| Widgets Inc.   |  2 |          10 |             5 |
| Widgets Inc.   |  2 |          10 |             5 |
| Frobizz PLC    |  3 |          10 |             5 |
| Frobizz PLC    |  3 |          10 |             5 |
| Frobizz PLC    |  3 |          10 |             5 |
| Frobizz PLC    |  3 |          10 |             5 |
+----------------+----+-------------+---------------+
12 rows in set (0.00 sec)

* /