如何在一个请求中计算总金额的金额和百分比

Question

我得到了两个表：

具有发件人 ID、发件人姓名列和

具有订单编号

、发件人编号列的订单。

我需要选择发货高于平均水平的发件人（到"发件人"列）、他们运送的订单数量（到"已发送的物品"列）和运输占总金额的百分比（到"配额"列，值必须带有"%"符号）。

我有两个选择：

 select Shippers.ShipperName, Orders.OrderID, Orders.OrderID
 from (
       select 
              Shippers.ShipperName as "Sender",
              count(Orders.OrderID) as "Items Sent",
              (count(Orders.OrderID)*100)/sum(Orders.OrderID) as "Quota"
       from Shippers
       left join Orders
       on Orders.ShipperID = Shippers.ShipperID
       group by Shippers.ShipperName
      ) Shippers 
       group by Shippers.ShipperName
  having Orders.OrderID > avg(Orders.OrderID);

结果 第 5 行的语法错误

第二个选项：

select Shippers.ShipperName as "Sender",
       count(Orders.OrderID) as "Items Sent",
       100*count(Orders.OrderID)/(select count(Orders.OrderID) 
                          from Shippers left join Orders
                          on Orders.ShipperID=Shippers.ShipperID) as "Quota"
from Shippers
left join Orders
on Orders.ShipperID = Shippers.ShipperID
group by Shippers.ShipperName
having count(Orders.OrderID)>(select count(Orders.OrderID)
            from Shippers left join Orders  
               on Orders.ShipperID = Shippers.ShipperID) /                 
               (select count(distinct ShipperID) from Shippers);

结果在第 13 行出现 1064 语法错误;

数据"托运人"： 数据"订单"：

ShippersID|ShipperName                        OrderID|ShipperID             
----------|----------------                 ---------|----------
       1  |Speedy Express                     10248  |    3
       2  |United Package                     10249  |    1
       3  |Federal Shipping                   10250  |    2
----------|----------------                   10251  |    1
                                              10252  |    2
                                              10253  |    2
                                              10254  |    2
                                              10255  |    3
                                              10256  |    2
                                            ---------|----------

Answer 1

我会将您的查询表述如下：

SELECT s.ShipperName,
       COUNT(*) AS num_orders,
       100 * COUNT(*) / (SELECT COUNT(*) FROM Shippers t1 LEFT JOIN Orders t2
                         ON t2.ShipperID = t1.ShipperID) AS num_orders_percent
FROM Shippers s
LEFT JOIN Orders o
    ON o.ShipperID = s.ShipperID
GROUP BY s.ShipperName
HAVING COUNT(*) > (SELECT COUNT(*) FROM Shippers t1 LEFT JOIN Orders t2 -- total # orders
                   ON t2.ShipperID = t1.ShipperID) /                    -- divided by
                  (SELECT COUNT(DISTINCT ShipperID) FROM Shippers)      -- total # shippers

我的查询中的所有内容对您来说都应该很熟悉，除了两个子查询，我将在这里解释：

SELECT COUNT(*) FROM Shippers t1 LEFT Orders t2 ON t2.ShipperID = t1.ShipperID

这将计算来自所有发件人的订单总数。 它用于计算给定托运人持有的订单百分比，如下所示：

% orders = # orders / total # orders

HAVING子句将结果集限制为仅超过平均订单数的发件人。 这里，需要另一个子查询：

SELECT COUNT(DISTINCT ShipperID) FROM Shippers

这将计算不同托运人的总数。 以下等式可用于查找每个托运人的平均订单数：

average # orders = total # orders / total # shippers

下面是使用您提供的示例数据的演示：

SQLFiddle

Answer 2

应该使用具有过滤平均值

 select t1.name, t1.count_order, t1.perc_order
 from (
  select 
      Shippers.ShipperName as name
    , count(Orders.OrderID) as  count_order
    , (count(Orders.OrderID)*100)/sum(Orders.OrderID) as  perc_order
  from Shippers
  left join Orders
  on Orders.ShipperID = Shippers.ShipperID
  Group by Shippers.ShipperName
  ) t1 
  group by t1.name 
  HAVING count_order > avg( count_order)