如何根据总订单价值和邮政组获取报告

Question

嗨，我正在尝试生成一份报告，其中订单低于 15 英镑和交付方式在一个表中按标准和 15-20 英镑订单和标准在另一个表中生成报告。 波纹管是我的查询。

[Customer no]
[Order No],
[Amount],
[Shipping Service]
[Shipping Amount]
FROM [Better$Internet Order] io
INNER JOIN [Better$Consignment] IC
ON IC.[Order No]=OH.[Order No]
WHERE   [order date] >= '2016-08-01' AND 
[order date] <= '2016-08-31' AND [COUNTRY]='UNITED KINGDOM' and oh.
[type] like 'order%' and  [Shipping Service]='Standard'
Group by 
[Customer no]
[Order No],
[Amount],
[Shipping Service]
[Shipping Amount]
ORDER BY [AMOUNT] ASC

在我的查询中，我给出了"客户编号"来获取客户总数，并给出了"订单号"来获取总订单，因为一个客户可能在一个月内订购几次。"订单日期"，"国家"和"类型"是订单，因此它只获得订单，而不是退款或更换。我只想得到，"运输服务"是标准配置。提前感谢您的帮助。

**table data:** 
Customer no| order No| Amount | Shipping Service
-------------------------------------------------
1   |    254  |   8    |     standard
2   |    258  |   12   |     Tracked
1   |    260  |   10   |     Standard
3   |    285  |   13   |     Tracked
4   |    295  |   11   |     Standard

Expected results: 
Month         Aug 2016      
Under 15 | Standard Delivery | Total customer  - 2   
Total orders    - 3 
total revenue   -  £18
AOV             - £9
Tracked Delivery  | Total customer  - 2   
Total orders    - 2 
total revenue   - 25
AOV             - 12.50

Answer 1

从内部开始，在派生表中，有一个case表达式来确定价格类。GROUP BY该结果来计算每个价格类别/运输类型：

select amountclass, "Shipping Service",
count(distinct "Customer no") as "Total customers",
count(*) as "Total orders",
sum("Amount") as "total revenue",
avg("Amount" * 1.0) as "AOV"
from
(
select "Customer no", "order No", "Amount", "Shipping Service"
case when "Amount" < 15 then "Under 15"
else "Over 15"
end as amountclass
from tablename
) dt
group by amountclass, "Shipping Service"
order by amountclass, "Shipping Service"

使用双引号(例如 分隔标识符"Shipping Service"是 ANSI SQL。(某些 dbms 产品还具有方括号作为替代项。