SQL计数计数结果
SQL计数计数结果
提问于 2019-11-07 04:11:58
我正在尝试从销售中生成报告，我想在数据演播室中显示。我有专栏电子邮件、order_number和total_price，我正在尝试使用sql查询识别以下内容：
- 重复和新客户数量(计算订单数量，如果订单>1，则重复，否则新订单)
- 来自重复和新客户的订单数量
Date | email | Order_number | Total Price
---------------------------------------------------------------
101019 | email1@email.com | 1111 | 100
101019 | email2@email.com | 1112 | 200
111019 | email3@email.com | 1113 | 130
111019 | email1@email.com | 1111 | 100 我所做的只是计算每封电子邮件的订单数量。
SELECT email, count(email) as order_count, date FROM orders GROUP BY
email这是我要展示的样本报告
Repeating Customer | New Customer
1 | 2
Orders from Repeating Cust. | Orders from New Customer
2 | 2
Total Price Repeating Cust. | Total Price New Custo.
200 | 330任何帮助都是非常感谢的。
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-11-07 05:29:01
您可以尝试Case和Sub查询：
SELECT SUM(CASE WHEN t.Cust_cnt=1 THEN 1 ELSE 0 END) AS New_Cust,
SUM(CASE WHEN t.Cust_cnt>1 THEN 1 ELSE 0 END) AS Repeat_Cust,
SUM(CASE WHEN t.Cust_cnt=1 THEN t.Total_Order ELSE 0 END) AS New_Cust_Order,
SUM(CASE WHEN t.Cust_cnt>1 THEN t.Total_Order ELSE 0 END) AS Repeat_Cust_Order,
SUM(CASE WHEN t.Cust_cnt=1 THEN t.Total_Price ELSE 0 END) AS New_Cust_Order_Total_Price,
SUM(CASE WHEN t.Cust_cnt>1 THEN t.Total_Price ELSE 0 END) AS Repeat_Cust_OrderTotal_Price,
t.Last_Order_Date
FROM (
SELECT COUNT(Email) AS Cust_cnt,SUM(O_Price) AS Total_Price,COUNT(O_Number) AS Total_Order, MAX(O_Date) AS Last_Order_Date, Email FROM Orders Group by Email) AS t;Stack Overflow用户
发布于 2019-11-07 04:38:51
使用两个单独的查询，一个用于重复查询，另一个用于新客户，然后简单地加入它们来创建报表。
重复客户
SELECT email, COUNT(email), SUM(TotalPrice) as order_count FROM orders GROUP BY email HAVING COUNT(email) > 1新客户
SELECT email, SUM(TotalPrice) as order_count FROM orders GROUP BY email HAVING COUNT(email) = 1如果您使用前端技术来显示这些结果，您也可以在前端将这些结果编译为水平方向(重复和新客户在两列而不是两行)。如果要在纯SQL中执行此操作，只需交叉连接这两个查询即可获得输出。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58741884
复制相关文章
相似问题
腾讯云开发者
