oracle group by can't get last payment_date, payment_sum

Question

I have the following query:

SELECT
  p.id,
  last_date_ps.pay_date last_pay_date
FROM projects p
  LEFT JOIN
  (
    SELECT
      pp.project_id,
      max(pp.pay_date) AS pay_date,
      pp.pay_sum
    FROM project_partuals pp
    WHERE pp.status IN (2, 4) AND pp.pay_sum > 0 AND pp.pay_date IS NOT NULL
    GROUP BY pp.project_id
  ) last_date_ps ON last_date_ps.project_id = p.id,
  contacts c
WHERE (p.debtor_contact_id = c.id)
ORDER BY priority_value DESC, name_f ASC;

and I get this error:

Error: ORA-00979: not a GROUP BY expression

SQLState:  42000
ErrorCode: 979
Position: 216

When I remove pp.pay_sum query works. How can I get in the left join (.... pay_date and pay_sum ORDER BY date DESC (Maximum date)?

score 0 · Answer 1 · answered Jan 24 '14 at 08:40

You have to include all other selected columns apart from constants and those in aggregate functions in your query if you are using group by clause, so include pp.pay_sum in it :

 select 
        p.id,
          last_date_ps.pay_date last_pay_date

from projects p
        left join
                    (select pp.project_id,max(pp.pay_date) as pay_date, pp.pay_sum 
                          from project_partuals pp
                          where pp.status in (2,4) and pp.pay_sum > 0 and pp.pay_date is not null 
                        group by pp.project_id, pp.pay_sum
                     ) last_date_ps on last_date_ps.project_id = p.id

      join contacts c on (p.debtor_contact_id=c.id)
order by priority_value desc, name_f asc

Daniel B · Accepted Answer · 2014-01-24T09:03:45.393

If you want pay_sum per project as a result from the inner query you need to aggregate it:

    (select pp.project_id, max(pp.pay_date) as pay_date, sum(pp.pay_sum) as  pay_sum
    from project_partuals pp 
    where pp.status in (2,4) and pp.pay_sum > 0 and pp.pay_date is not null 
    group by pp.project_id ) last_date_ps

If you want only the last payment per project, the inner query should be:

(select project_id, pay_date, pay_sum FROM
  (select pp.project_id, pp.pay_date, pp.pay_sum, 
   row_number() over (PARTITION  by pp.project_id order by pp.pay_date desc) rn
   from project_partuals pp  where pp.status in (2,4) and pp.pay_sum > 0 and pp.pay_date is not null 
  ) X where X.rn = 1)

But when I add it in group by the query returns not ONLY the last payment (for each project_id) but ALL the payments for each project_id. I need ONLY the last (by date DESC) pay_sum and pay_date — Konstantinos, Jan 24 '14 at 08:45

oracle group by can't get last payment_date, payment_sum

2 Answers2