0

Hi,

I have a java appllication which has a query depending on the requirement,

select EmployeeID, TransactionAmount from employee where EmployeeID IN ('E123','E234') group by EmployeeID;

which works well in mysql, but for oracle a get an error like
[Err] ORA-00979: not a GROUP BY expression

Can anyone help me in this.?

Thanks

4
Contributors
26
Replies
27
Views
5 Years
Discussion Span
Last Post by arshadshabbir
0

Try this.

select EmployeeID, sum(TransactionAmount) as TotalAmount from employee where EmployeeID IN ('E123','E234') group by EmployeeID;
0

I actually want to select the last transaction amount,

so my query looks like

select EmployeeID, TransactionAmount from employee where EmployeeID IN ('E123','E234') group by EmployeeID ORDER BY TransactionDate DESC;

0

You can't use GROUP BY clause without using any grouping function in Oracle.

Is there any other way i can achieve this.

0

I want last transaction amount for every employee id. So i have group it by employeeID.

0

last transaction amount is the latest transaction amount an employee has transacted for an employee table.

0

how is your structure

1) is there any possibility that an employee transacted more than once in same date
or
2)for one date employee will have only one transaction

select your case 1 or 2

1

I assume your employee table has datetime field named trans_date_time (you must change it to exact name at 2 place in following query).

select a.EmployeeID, TransactionAmount  from 
employee a left outer join (
select EmployeeID, max(trans_date_time)  as max_dt_time
 from employee where EmployeeID IN ('E123','E234') group by EmployeeID) b 
on a.employeeid=b.employeeid and a.trans_date_time=b.max_dt_time

If you do not have time component and if employees has more than one transaction in same date then this query will fail

Edited by urtrivedi: n/a

Votes + Comments
agree
0

if i dont use group by clause then i get many records for every employee.??

0

Try following Query using Analytical function.......

select employeeid,TransactionAmount,max_date  from
(
select EmployeeID, TransactionAmount,TransactionDate,
max(TransactionDate) over (partition by employeeid order by employeeid) max_date
from employee
)
where TransactionDate = max_date 
order by employeeid;

Edited by Ezzaral: Added code tags. Please use them to format any code that you post.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.