Group by clause in Oracle

[divyakprabh]

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

[debasisdas]

Try this.

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

[divyakprabh]

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;

[debasisdas]

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

[divyakprabh]

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

Is there any other way i can achieve this.

[debasisdas]

why you need to use GROUP BY to select the last transaction amount ?

[divyakprabh]

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

[debasisdas]

How you define last transaction amount ?

[divyakprabh]

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

[debasisdas]

So there will be only one last traction for any employee, right ?

[divyakprabh]

There can be many transactions for a single employee.

[debasisdas]

But there can be only one last traction .

[divyakprabh]

Yes, only one last transaction amount.

[urtrivedi]

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

[debasisdas]

Yes, only one last transaction amount.

Then why you need GROUP BY ?

[urtrivedi]

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

[divyakprabh]

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

[debasisdas]

That is because you are not checking for last transaction in your code.

[debasisdas]

That is because you are not checking for last transaction in your code.

[divyakprabh]

How will i be able to do it

[urtrivedi]

Have you read my post

http://www.daniweb.com/web-development/databases/oracle/threads/379241/1634099#post1634099

[divyakprabh]

but left outer join gives bad performance right

[debasisdas]

who told you ?

[divyakprabh]

as per what i know... Is it not so.???

[debasisdas]

no.

[divyakprabh]

Thanks for the help..

[arshadshabbir]

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;