选择case语句添加信息

Question

我有两张桌子，纸制信息和工资。在我的发言中，我加入了CONT_ID。

 select (case when age_years >= 18 and age_years < 30 then '18-29'
            when age_years < 50 then '30-49'
            when age_years < 70 then '50-69'
             when age_years < 100 then '70-100'
            end) as age_range,
             count(DISTINCT c.CONT_ID) as num,
     CASE WHEN GENDER = '1' THEN 1 ELSE 0 END / COUNT(DISTINCT c.CONT_ID),
     SUM(ACAUREQ_AUREQ_TX_DT_TTLAMT) / COUNT(*) 'TOTAL-Amount Avg' 
         from cardinfo c
         left join paytb t
          on c.CONT_ID = t.CONT_ID
      group by (case when age_years >= 18 and age_years < 30 then '18-29'
         when age_years < 50 then '30-49'
         when age_years < 70 then '50-69'
         when age_years < 100 then '70-100'
         end)
         order by min(age_years);       

下面显示了以下的前2列：

 AGE_RANGE          NUM      GENDER      
---------+---------+----------------
18-29              828        50%        
30-49             2510        ??         
50-69             2014        ??         
70-100             649                

我想从表中添加性别参数(0或1)。然而，这是行不通的：

CASE WHEN GENDER = '1' THEN 1 ELSE 0 END / COUNT(DISTINCT c.CONT_ID)  

sqlcode= -122

我试着找出交易的平均值：

SUM(ACAUREQ_AUREQ_TX_DT_TTLAMT) / COUNT(*) 'TOTAL-Amount Avg' 

不幸的是，这会给我一个错误。

金额如下：

  SUM(ACAUREQ_AUREQ_TX_DT_TTLAMT)
    -----+---------+---------+---------+
    +1.232071426000000000000000E+0007   
    +3.062581021000000000000000E+0007   
    +2.399951792000000000000000E+0007   
    +8.228919170000000000000000E+0006   

Answer 1

我会将原始查询打包到派生表中，以保存一些类型：

select age_range,
       count(DISTINCT CONT_ID) as num,
       CASE WHEN GENDER = '1' THEN 1 ELSE 0 END / COUNT(DISTINCT c.CONT_ID),
       SUM(ACAUREQ_AUREQ_TX_DT_TTLAMT) / COUNT(*) 'TOTAL-Amount Avg' 
from
(
    select (case when age_years >= 18 and age_years < 30 then '18-29'
                 when age_years < 50 then '30-49'
                 when age_years < 70 then '50-69'
                 when age_years < 100 then '70-100'
            end) as age_range,
            c.CONT_ID,
            GENDER,
            ACAUREQ_AUREQ_TX_DT_TTLAMT
    from CARDUSR.CLIENT_INFO c
    left join cardusr.sppaytb t
        on c.CONT_ID = t.CONT_ID
)
group by age_range, GENDER

Answer 2

您需要将gender包含在group by中，或者将其作为聚合函数的参数：

SUM(CASE WHEN GENDER = '1' THEN 1 ELSE 0 END) / COUNT(DISTINCT c.CONT_ID)

但是，您可能不需要COUNT(DISTINCT)。如果没有必要，可以将其简化为：

AVG(CASE WHEN GENDER = '1' THEN 1.0 ELSE 0 END)