SQL通过语法计算了组

Question

所以，我有此shell命令，该命令从选择中创建CSV，但这并不重要。我遇到的问题是，我选择的一个字段是作为日期字段yyyymmdd的整数字段。因此，我要做的是选择字段/100以获取Yyyymm和Group，其余的字段并总和最后一个。

    SELECT trim(a.F1)||chr(44)||trim(b.F2)||chr(44)||trim(F3)||chr(44)
        ||trim(F4)||chr(44)|||CAST(plandate/100 AS INT)||chr(44)
        ||trim(F5)||chr(44)||trim(F6)||chr(44)||trim(true_dem)
        as "F1, F2, F3, F4, YYYYMM, F5, F6, MONTHLY_DEMAND" 
      from T1 a 
      join T2 b on a.ASDF = b.ASDF 
      WHERE PLANDATE > 20180400

所以，正如您现在可以看到的那样"字段，但是我不知道该日期要分组，因为它是经计算的(Plandate/100 as Int(

我尝试这样做

    SELECT trim(a.f1)||chr(44)||trim(b.f2)||chr(44)||trim(f3)||chr(44)
        ||trim(f4)||chr(44)||CAST( plandate/100 AS INT)||chr(44)
        ||trim(f5)||chr(44)||trim(f6)||chr(44)||sum(true_dem)
        as "f1, f2, f3, f4, YYYYMM, f5, f6, MONTHLY_DEMAND" 
      from t1 a 
      join t2 b on a.f1 = b.f1 
      WHERE PLANDATE > 20180400
      group by a.f1, f2, f3, f4, f5, f6, (CAST( plandate/100 AS INT))

，这显然是不正确的，因为我在选择列表中获得[SQL0122]列Plandate或表达式无效。错误。几乎只是说这是不好的。如果可能

忽略FX和TX的内容，我只想拼出公司数据库内容的任何提示。如果很重要

这也不会按照您想要的列标头输出：

SELECT   
  a.f1, b.f2, f3, f4, CAST( plandate/100 AS INT) as YYYYMM, 
  f5, f6, sum(true_dem) as MONTHLY_DEMAND
from t1 a join t2 b on a.f1 = b.f1 
WHERE PLANDATE > 20180400
group by a.f1, f2, f3, f4, f5, f6, (CAST( plandate/100 AS INT))

如果您认为需要做自己的方式，请首先使用子选择来进行聚合：

SELECT   
    trim(f1)||chr(44)||trim(f2)||chr(44)||trim(f3)||chr(44)||trim(f4)||chr(44)
    ||CAST( plandate/100 AS INT)||chr(44)||trim(f5)||chr(44)||trim(f6)||chr(44)
    ||sum(true_dem) 
    as "f1, f2, f3, f4, YYYYMM, f5, f6, MONTHLY_DEMAND"
FROM (
  SELECT 
    a.f1, b.f2, f3, f4, CAST( plandate/100 AS INT) as YYYYMM, 
    f5, f6, sum(true_dem) as MONTHLY_DEMAND
  from t1 a join t2 b on a.f1 = b.f1 
  WHERE PLANDATE > 20180400
  group by a.f1, f2, f3, f4, f5, f6, (CAST( plandate/100 AS INT))
) t