asked on

sum over partition error

hi
i try to use this sql

select
   acc_no, cost_center, 
( credit - debit) cur_balance, 0 prev_balance,

DECODE (acc_no, 41010, (credit - debit)) cur_sal, 0 prev_sal,
                      DECODE (acc_no, 41010, 0, (debit - credit))
                         cur_all_exp, 0 prev_all_exp

 from alpha_tran where
period =  to_date('2015-06-30','yyyy-mm-dd')

Open in new window

it is ok
then i added summary like this

SELECT cost_center,acc_no,
  (sum (cur_sal) over (partition by cost_center)) sum_sal
                     
from
(
select
   acc_no, cost_center, 
( credit - debit) cur_balance, 0 prev_balance,

DECODE (acc_no, 41010, (credit - debit)) cur_sal, 0 prev_sal,
                      DECODE (acc_no, 41010, 0, (debit - credit))
                         cur_all_exp, 0 prev_all_exp

 from alpha_tran where
period =  to_date('2015-06-30','yyyy-mm-dd')
)
group by cost_center,acc_no

Open in new window

it goes to the line 10
(credit - debit))
not a group by expression

Sean Stuber

remove the group by

if the produces duplicates, then use distinct

SELECT cost_center,acc_no,
(sum (cur_sal) over (partition by cost_center)) sum_sal

from
(
select
acc_no, cost_center,
( credit - debit) cur_balance, 0 prev_balance,

DECODE (acc_no, 41010, (credit - debit)) cur_sal, 0 prev_sal,
DECODE (acc_no, 41010, 0, (debit - credit))
cur_all_exp, 0 prev_all_exp

from alpha_tran where
period = to_date('2015-06-30','yyyy-mm-dd')
)

or, you can get rid of the subquery since it's generating values you're not using
and just query the part you want directly

SELECT cost_center, acc_no, SUM(CASE WHEN acc_no = 41010 THEN credit - debit END) OVER (PARTITION BY cost_center)
FROM alpha_tran
WHERE period = TO_DATE('2015-06-30', 'yyyy-mm-dd')

again, add DISTINCT if necessary

PortletPaul

SUM() without OVER() DOES require a group by clause.
In this form SUM() is an AGGREGATE FUNCTION

SELECT
       cost_center
     , acc_no
     , sum(cur_sal) sum_sal
FROM (
     SELECT
            acc_no
          , cost_center
          , (credit - debit) cur_balance
          , 0 prev_balance
          , DECODE(acc_no, 41010, (credit - debit)) cur_sal
          , 0 prev_sal
          , DECODE(acc_no, 41010, 0, (debit - credit)) cur_all_exp
          , 0 prev_all_exp
     FROM alpha_tran
     WHERE period = to_date('2015-06-30', 'yyyy-mm-dd')
     )
GROUP BY
       cost_center
     , acc_no;

Open in new window

But when you use OVER(...) you do NOT require a GROUP BY.
In this form SUM() is an ANALYTIC FUNCTION

SELECT
       cost_center
     , acc_no
     , (sum(cur_sal) OVER (PARTITION BY cost_center)) sum_sal
FROM (
     SELECT
            acc_no
          , cost_center
          , (credit - debit) cur_balance
          , 0 prev_balance
          , DECODE(acc_no, 41010, (credit - debit)) cur_sal
          , 0 prev_sal
          , DECODE(acc_no, 41010, 0, (debit - credit)) cur_all_exp
          , 0 prev_all_exp
     FROM alpha_tran
     WHERE period = to_date('2015-06-30', 'yyyy-mm-dd')
     )

Open in new window

A fundamental difference between the two uses is that:
AGGREGATE functions WILL reduce the number of rows (due to the group by)
ANALYTIC functions WILL NOT REDUCE NUMBER OF ROWS (because there is no group by)

It isn't exactly clear why you have several columns in the subquery that aren't referenced in the outer (top level) query, perhaps that was just an abbreviation you introduced for this question? If you really do not need all those columns then I too would recommend simplification. In addition personally I would avoid use of DECODE as I much prefer to use CASE EXPRESSIONS.

My guess at the overall query is

SELECT
       cost_center
     , sum(case when acc_no = 41010 then (credit - debit) else 0 end) sum_sal
     , sum(case when acc_no = 41010 then 0 else (credit - debit) end) sum_all_exp
     , sum(credit - debit) sum_balance
     /*
          are there really needed?
          , 0 prev_balance
          , 0 prev_sal
          , 0 prev_all_exp
     */
FROM alpha_tran
WHERE period = to_date('2015-06-30', 'yyyy-mm-dd')
GROUP BY
       cost_center

Open in new window

{+edit}BUT this guess removes the account number column, so I'm not confident about that guess.