的Oracle SQL SELECT SUM当值与其他GROUP BY子句不同

Question

我有以下SAMPLE_DATA数据集：

ORD | POS | INSGRP | INS | DELI | DELPOS 
------------------------------------------------------- 
1 | 'Pos11' | '11' | 11 | 'deli111' | 'DelPos1111' 
1 | 'Pos11' | '11' | 11 | 'deli112' | 'DelPos1121' 
1 | 'Pos11' | '11' | 11 | 'deli112' | 'DelPos1122' 

2 | 'Pos21' | '21' | 21 | 'deli211' | 'DelPos2111' 
2 | 'Pos21' | '22' | 22 | 'deli221' | 'DelPos2211' 
2 | 'Pos21' | '22' | 22 | 'deli221' | 'DelPos2212' 

3 | 'Pos31' | '31' | 31 | 'deli311' | 'DelPos3111' 
3 | 'Pos32' | '31' | 31 | 'deli321' | 'DelPos3211' 
3 | 'Pos32' | '31' | 31 | 'deli321' | 'DelPos3212' 
3 | 'Pos32' | '31' | 31 | 'deli322' | 'DelPos3221' 

• 当有许多销售点的ORD，只有一个INSGRP
• 当只有一个为ORD POS，可以有很多INSGRP
• 这是我的实际要求的一个简单的版本，只有揭露这个问题
• 一个ORD可以有很多DELI
• 一个DELI可以有很多DELPOS
• 德利DELPOS在那里添加复制ORD-POS-INSGRP-INS

我要总结取决于ORD，POS和INSGRP INS列行。

• 当只有一个POS的INSGRP时，我希望INS保持不变。
• 当有许多INSGRP的POS，我想不同的INSGRP的INS的总和为POS

所以我想下面的结果：

ORD | SUM(INS) 
----------------------------------------------------------------------- 
1 | 11 
One INSGRP for one POS, so INS stay the same 11 
2 | 43 
Two INSGRP for one POS, so INS is the SUM of the INSGRP 21 and 22 so 43 
3 | 31 
One INSGRP for two POS, so INS stay the same 31 

我试过如下：

SELECT ord, 
     SUM (ins) AS ins 
FROM sample_data 
GROUP BY ord, pos 
ORDER BY ord; 

，得到了这样的结果：

ORD | INS 
--------- 
1 | 33 
2 | 65 
3 | 31 
3 | 93 

所有内容总结在一起，并且每个ORD 3的INSGRP都有一行。然后，我将INS添加到GROUP BY子句中，但它只在两行中分隔ORD 2。

我试图用一切分组，并把一所独特的开头：

SELECT UNIQUE ord, 
     SUM (ins) AS ins 
FROM sample_data 
GROUP BY ord, pos, deli, DelPos 
ORDER BY ord; 

，结果是：

ORD | INS 
--------- 
1 | 11 
2 | 21 
2 | 22 
3 | 31 

这是我从我想要的结果得到的最接近。唯一缺少的部分是将ORD 2分组在一行上并对INS进行求和。

而且，我希望能像分区组：

SUM (ins) OVER (PARTITION BY ord, pos, deli, DelPos) AS ins 

有人可以帮助我？

---

这里的要求丝毫SAMPLE_DATA：

WITH sample_data 
    AS (SELECT 1 ord, 'Pos11' pos, '11' insGrp, 11 ins, 'deli111' deli, 'DelPos1111' DelPos FROM DUAL 
     UNION ALL 
     SELECT 1 ord, 'Pos11' pos, '11' insGrp, 11 ins, 'deli112' deli, 'DelPos1121' DelPos FROM DUAL 
     UNION ALL 
     SELECT 1 ord, 'Pos11' pos, '11' insGrp, 11 ins, 'deli112' deli, 'DelPos1122' DelPos FROM DUAL 
     UNION ALL 

     SELECT 2 ord, 'Pos21' pos, '21' insGrp, 21 ins, 'deli211' deli, 'DelPos2111' DelPos FROM DUAL 
     UNION ALL 
     SELECT 2 ord, 'Pos21' pos, '22' insGrp, 22 ins, 'deli221' deli, 'DelPos2211' DelPos FROM DUAL 
     UNION ALL 
     SELECT 2 ord, 'Pos21' pos, '22' insGrp, 22 ins, 'deli221' deli, 'DelPos2212' DelPos FROM DUAL 
     UNION ALL 

     SELECT 3 ord, 'Pos31' pos, '31' insGrp, 31 ins, 'deli311' deli, 'DelPos3111' DelPos FROM DUAL 
     UNION ALL 
     SELECT 3 ord, 'Pos32' pos, '31' insGrp, 31 ins, 'deli321' deli, 'DelPos3211' DelPos FROM DUAL 
     UNION ALL 
     SELECT 3 ord, 'Pos32' pos, '31' insGrp, 31 ins, 'deli321' deli, 'DelPos3212' DelPos FROM DUAL 
     UNION ALL 
     SELECT 3 ord, 'Pos32' pos, '31' insGrp, 31 ins, 'deli322' deli, 'DelPos3221' DelPos FROM DUAL) 
SELECT UNIQUE ord, 
    SUM (ins) AS ins 
FROM sample_data 
GROUP BY ord, pos, deli, DelPos 
ORDER BY ord; 

还是创造并插入

create table tbl (ord number, pos varchar2(10), insGrp varchar2(10), ins number, deli varchar2(10), DelPos varchar2(10)) 

insert into tbl (SELECT 1 ord, 'Pos11' pos, '11' insGrp, 11 ins, 'deli111' deli, 'DelPos1111' DelPos FROM DUAL 
     UNION ALL 
     SELECT 1 ord, 'Pos11' pos, '11' insGrp, 11 ins, 'deli112' deli, 'DelPos1121' DelPos FROM DUAL 
     UNION ALL 
     SELECT 1 ord, 'Pos11' pos, '11' insGrp, 11 ins, 'deli112' deli, 'DelPos1122' DelPos FROM DUAL 
     UNION ALL 

     SELECT 2 ord, 'Pos21' pos, '21' insGrp, 21 ins, 'deli211' deli, 'DelPos2111' DelPos FROM DUAL 
     UNION ALL 
     SELECT 2 ord, 'Pos21' pos, '22' insGrp, 22 ins, 'deli221' deli, 'DelPos2211' DelPos FROM DUAL 
     UNION ALL 
     SELECT 2 ord, 'Pos21' pos, '22' insGrp, 22 ins, 'deli221' deli, 'DelPos2212' DelPos FROM DUAL 
     UNION ALL 

     SELECT 3 ord, 'Pos31' pos, '31' insGrp, 31 ins, 'deli311' deli, 'DelPos3111' DelPos FROM DUAL 
     UNION ALL 
     SELECT 3 ord, 'Pos32' pos, '31' insGrp, 31 ins, 'deli321' deli, 'DelPos3211' DelPos FROM DUAL 
     UNION ALL 
     SELECT 3 ord, 'Pos32' pos, '31' insGrp, 31 ins, 'deli321' deli, 'DelPos3212' DelPos FROM DUAL 
     UNION ALL 
     SELECT 3 ord, 'Pos32' pos, '31' insGrp, 31 ins, 'deli322' deli, 'DelPos3221' DelPos FROM DUAL) 

Answer 1

我想你想要这样的：

SELECT ord, 
    CASE WHEN COUNT (DISTINCT pos) = 1 THEN SUM(DISTINCT ins) ELSE MIN(ins) END AS ins 
FROM sample_data 
GROUP BY ord 
ORDER BY ord; 

ORD INS 
--- ------ 
    1  11 
    2  43 
    3  31