根据特定条件 SQL 计算每个组的贡献百分比

Question

我想计算 2 组的"数量"贡献百分比，第 1 组"客户 ID"出现多次，第 2 组"客户 ID"出现一次。例如：

|ID | OrderID   |Quantity
|---|--------   |-----
|1  |10248      |12
|2  |10248      |10 
|3  |10248      |5 
|4  |10249      |9
|5  |10249      |40
|6  |10250      |10

-----到-----

OrderID |Quantity
10248   |27
10249   |49
10250   |10

-----最终进入-----

Group    % tot Quantity
1         12%   <--- Just OrderID 10250 since it has only 1 order
2         88%

Answer 1

select
100.0 * sum(case when cnt = 1  then sumQty else 0 end) / sum(sumQty) as "single Order"
,100.0 * sum(case when cnt <> 1 then sumQty else 0 end) / sum(sumQty) as "multiple Orders"
from
(
select 
OrderID
,sum(Quantity) as sumQty
,count(*) as cnt
from myTable
group by OrderId
) as dt

Answer 2

也许这会有所帮助。否则有任何错误请通知我。

select a.OrderID,a.grp, Cast(Cast(a.Quantity*100 as decimal(18,2)) as varchar(100)) + ' %' as Percentage from (
select OrderID as OrderID,count(OrderID) as grp,Sum(Quantity) as Quantity  from myTable
group by OrderID
) as a

Answer 3

其他答案将值放入一行，但 OP 要求两行。 如果这很重要：

select (case when cnt = 1 then 1 else 2 end) as grp,
qty / totQty as ratio
from (select OrderID, sum(Quantity) as Qty, count(*) as cnt
from myTable
group by OrderId
) o cross join
(select sum(Quantity) as totQty from myTable) x
group by (case when cnt = 1 then 1 else 2 end);

Answer 4

以前的答案不起作用，它回复 33% 这项工作很好：

select 100.0 * sum(case when cnt = 1  then sumQty else 0 end) / sum(sumQty) as 'single Order'
,100.0 * sum(case when cnt <> 1 then sumQty else 0 end) / sum(sumQty) as 'multiple Order'
from
(
select 
OrderID
,sum(Quantity) as sumQty -- not needed for the final result
,count(*) as cnt
from _ds_test_ac
group by OrderId
) as dt

如果你想在两行中，你可以做到：

select cnt = 1 as 'IS Single',   100.0 * sum(sumQty) / tot as 'percent'
from
(
select 
OrderID
,sum(Quantity) as sumQty -- not needed for the final result
,count(*) as cnt
from _ds_test_ac
group by OrderId
) as dt join 
(
select sum(Quantity) as tot
from _ds_test_ac
) dtot 
group by 1