时间戳

首页 > 解决方案 > SQL 按日期组合所有数据

sql - SQL 按日期组合所有数据

问题描述

我正在尝试按日期合并所有数据。它的销售数据有促销代码和没有。我努力了：

SELECT Order Date, sum(Promo Code), sum(Sales Amount)
FROM Data

这在 SQL 中可能吗？

数据

+---------------+------------+--------------+
| Order Date    | Promo Code | Sales Amount | 
+---------------+------------+--------------+
| 10-29-20      |   today20  |   50         |  
+---------------+------------+--------------+
| 10-29-20      |   vip20    |   50         |   
+---------------+------------+--------------+
| 10-29-20      |   today20  |   50         |  
+---------------+------------+--------------+
| 10-28-20      |   vip20    |   50         |   
+---------------+------------+--------------+
| 10-28-20      |   vip20    |   50         |   
+---------------+------------+--------------+
| 10-27-20      |   pc20     |   25         |
+---------------+------------+--------------+
| 10-28-20      |            |   50         |   
+---------------+------------+--------------+
| 10-28-20      |   vip20    |   50         |   
+---------------+------------+--------------+
| 10-27-20      |            |   25         |
+---------------+------------+--------------+
| ....          |      ....  |   ....       |
+---------------+------------+--------------+
| ....          |      ....  |   ....       |
+---------------+------------+--------------+

我想要发生的事情

Output
+---------------+--------------------+--------------+
| Order Date    | Promo Code         | Sales Amount | 
+---------------+--------------------+--------------+
| 10-29-20      |   today20, vip20   |   150        |  
+---------------+--------------------+--------------+
| 10-28-20      |   vip20            |   100        |   
+---------------+--------------------+--------------+
| 10-29-20      |   today20          |   50         |  
+---------------+--------------------+--------------+
| 10-28-20      |   vip20            |   50         |   
+---------------+--------------------+--------------+
| 10-28-20      |   null             |   50         |   
+---------------+--------------------+--------------+
| 10-27-20      |   null             |   25         |
+---------------+--------------------+--------------+

我不确定这是否可能....

标签： sql

解决方案

嗯。. . 您想按两列分组：

SELECT Order_Date, Promo_Code, sum(Sales Amount)
FROM Data
GROUP BY Order_Date, Promo_Code;

推荐阅读