1 Reply Latest reply: Oct 25, 2012 7:31 AM by user8819407 RSS

    SQL query problem - select max (case... aggregate function)

    user8819407
      Hi,
      I have a problem with below sql query, it gives me problem/error message 'ORA-00937: not a single-group group function', why?

      select sag.afdeling, sag.sagsnr, to_char(sag.start_dato, 'yyyy-mm-dd'), sag.stat, BOGF_TRANS.TRANSTYPE,
      max (case when BOGF_TRANS.TRANSTYPE = 'K' then sum(bogf_trans.belobdkk) end) + -- as "TRANSTYPE K",
      max (case when BOGF_TRANS.TRANSTYPE = 'D' then sum(bogf_trans.belobdkk) end) as "TRANSTYPE K & D",
      max (case when BOGF_TRANS.TRANSTYPE = 'S' then sum(bogf_trans.belobdkk) end) as "SUM TRANSTYPE S"
      from sag
      join bogf_trans on sag.selskab = bogf_trans.selskab and sag.sagsnr = bogf_trans.sagsnr and sag.afdeling = bogf_trans.afdeling
      where SAG.SELSKAB=37 and SAG.AFDELING = 'SUS' AND SAG.SAGSNR = 10876
      group by sag.afdeling, sag.sagsnr, sag.start_dato, sag.stat, BOGF_TRANS.TRANSTYPE

      If I exclude (columns) as below it give me correct summations (max (case... sum(...)) but then I miss some important info that I need

      select
      max (case when BOGF_TRANS.TRANSTYPE = 'K' then sum(bogf_trans.belobdkk) end) + -- as "TRANSTYPE K",
      max (case when BOGF_TRANS.TRANSTYPE = 'D' then sum(bogf_trans.belobdkk) end) as "TRANSTYPE K & D",
      max (case when BOGF_TRANS.TRANSTYPE = 'S' then sum(bogf_trans.belobdkk) end) as "SUM TRANSTYPE S"
      from sag
      join bogf_trans on sag.selskab = bogf_trans.selskab and sag.sagsnr = bogf_trans.sagsnr and sag.afdeling = bogf_trans.afdeling
      where SAG.SELSKAB=37 and SAG.AFDELING = 'SUS' AND SAG.SAGSNR = 10876
      group by sag.afdeling, sag.sagsnr, sag.start_dato, sag.stat, BOGF_TRANS.TRANSTYPE

      Any ideas?