SQL query problem - select max (case... aggregate function)
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?