title: Group by clause with multiple joins which both column have as of date
body: <p>i am trying to do a <code>group by</code> clause for <code>as_of_date</code> instead of <code>inner join</code>. i was wondering if this possible , i have tried below query but its prompting me error</p>
  <p>When i tried using <code>main.as_of_date = ref.as_of_date</code> , it is working perfectly , however my objective is further optimise my query with <code>group by</code> clause</p>
  <pre><code>select 
  'Wholesale Retail' as business_segment
  , 'Deposit' as product
  , main.as_of_date
  , 'MY' as country
  , ref.GFCID as gfcid
  , 'DCIP_GFCID' as gfcid_typ
  , cast(arrg_id as String) as arrg_id
  , cast(arrg_desc as String) as arrg_desc
  , 'NA' as interest_rate
  , cast(ARRG_LIFE_CYC_STA_TYP_CD  as String) as ARRG_LIFE_CYC_STA_TYP_CD
  , 'ME' as feed_period
  , 'S_LR_DEP_MY_WR_INB_ME' as eap_table_name  
  
  from gflmyasg_staging.S_LR_DEP_MY_WR_INB_ME main 
  left join gflmyasg_staging.S_LR_CMN_MY_WS_GFCID ref 
      on main.DCIP_GFCID=ref.GFCID 
  group by ref.as_of_date and main.as_of_date
  </code></pre>
  <p>I have using this query but it returned error</p>
  <pre><code>select 
  'Wholesale Retail' as business_segment
  , 'Deposit' as product
  , main.as_of_date
  , 'MY' as country
  , ref.GFCID as gfcid
  , 'DCIP_GFCID' as gfcid_typ
  , cast(arrg_id as String) as arrg_id
  , cast(arrg_desc as String) as arrg_desc
  , 'NA' as interest_rate
  , cast(ARRG_LIFE_CYC_STA_TYP_CD  as String) as ARRG_LIFE_CYC_STA_TYP_CD
  , 'ME' as feed_period
  , 'S_LR_DEP_MY_WR_INB_ME' as eap_table_name  
  from gflmyasg_staging.S_LR_DEP_MY_WR_INB_ME main 
  left join gflmyasg_staging.S_LR_CMN_MY_WS_GFCID ref 
      on main.DCIP_GFCID=ref.GFCID 
      and main.as_of_date=ref.as_of_date 
  group by main.as_of_date;
  </code></pre>
  <p>Error : AnalysisException: select list expression not produced by aggregation output (missing from GROUP BY clause?): ref.GFCID gfcid</p>
  <p>I have tried researching on the usage of group by and found we can use group by main.as_of_date. what is the better approach on this or there is something that i missed out?</p>