Sql Calculated Group By syntax possibility

So, I have this shell command that creates a csv from a select, but that's not really important. The problem I'm having is that one of the fields i'm selecting is an integer field working as a date field yyyymmdd. So what I want to do is select the field / 100 to get the yyyymm and group by it and the rest of the fields and sum the last one.

    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

So, as you can see right now i'm grabbing all of the dates after a specific date but I don't have the group by logic in, the reason for this is because I can group by all of the "F" fields, but I don't know what to group by for the date filed since it is calculated CAST( plandate/100 AS INT)

I tried doing it like this

    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))

but that's obviously not right as i get the [SQL0122] Column PLANDATE or expression in SELECT list not valid. error. pretty much just saying that it's no good. I'd really like to keep this alias if possible so that i can export it to csv with column headings

ignore the fx and tx stuff I just wanted to chop out any hints to company database stuff. If it matters I'm running this on I-series DB2 through qsh

Solution

Wouldn't this also output your column headers as you want them:

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))

If you think you need to do it your way, simply use a subselect to do the aggregation first:

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