group by clause in oracle10g

Question

I am using oracle 10g and wrote a query like this:

SELECT presenttime,
       employeeid
  FROM mobilelocation
 WHERE presentdate = '31-10-2011'
 GROUP BY employeeid;

resulting an error message

ORA-00979: not a GROUP BY expression

But upon removing this group clause returining the result without any errors like this,

PRESENTTIME EMPLOYEEID
23:25   12304
23:48   12305

What is the problem with my group by clause and also suggest me how to use order by clause also

score 5 · Accepted Answer · answered Nov 09 '11 at 02:46

GROUP BY implies that you want to aggregate multiple rows from the table into a single row for every EmployeeID value. If that is your goal, you'd need to specify an aggregate function on the columns that you are not grouping by. If you want the earliest PresentTime value for each EmployeeID, for example, you'd do something like

SELECT EmployeeID, MIN(PresentTime) earliestPresentTime
  FROM mobileLocation
 WHERE PresentDate = date '2011-10-31'
 GROUP BY EmployeeID

score 1 · Answer 2 · answered Nov 09 '11 at 02:44

You need to group by both presenttime, employeeID

As far as order by; its similar to group by.

Order by employeeID, presentTIme (desc)

any item in the select portion must be in the group by portion if an aggregrate wasn't defined (min, max, sum, count, etc.)

group by clause in oracle10g

2 Answers2