How to get all the columns, but have to group by only 2 columns in sql query

Question

I have a table Employees, which has Fields as below:

Employee_name,Employee_id,Employee_status,Employee_loc,last_update_time.

This table does not have any constraint. I have tried the below query.

select Employee_name, count(1) 
from Employees 
where Employee_status = 'ACTIVE' 
Group by Employee_name,Employee_loc 
having count(Employee_name) > 1 
order by count(Employee_name)  desc

In the select, I need to get Employee_id too.. Can any one help on how to get that?

If there are ten employees with the same name and location, which `employee_id` do you want? Do you want all 10? Just the min (or the max)? If you want all 10, do you want 10 rows to be returned? — Justin Cave, Apr 03 '13 at 10:24
I need all the 10 employee_ids. ( Ideally it will get the employee list having same location and name with Active status.) — Muthukumar, Apr 03 '13 at 10:56

score 4 · Answer 1 · 2013-04-03T11:02:31.883

4

You can just add Employee_id to the query, and also add it to the group by clause. (Adding it to the grouping won't make any difference in the query results, assuming each employee name each employee id is unique).

If the grouping does make a difference, that implies that some combinations of employee name and location have more than one ID associated with them. Your query would therefore need to decide which ID to return, possibly by using an aggregate function.

edited Apr 03 '13 at 11:02

answered Apr 03 '13 at 10:27

I cannot,, If i add employee_id also in group by, then it is trying to find the unique combination of all the three fields( Employeed_name,Employee_loc, Employee_id).. I need the unique combination for Employee_name and Employee_loc.. – Muthukumar Apr 03 '13 at 10:57
@Muthukumar, if it makes a difference this means that some combinations of `employee_name, empolyee_loc` have more than one employee_id. Then how do you know which one you want? – Apr 03 '13 at 11:00
I need all those employee_ids.. Ideally it will get the employee list having same location and name with Active status – Muthukumar Apr 03 '13 at 11:07

score 1 · Answer 2 · answered Apr 03 '13 at 10:28

1

SELECT EMPLOYEE_NAME, EMPLOYEE_ID, COUNT(1)
FROM
EMPLOYEES
WHERE
EMPLOYEE_NAME IN
(
SELECT EMPLOYEE_NAME
FROM EMPLOYEES
WHERE Employee_status = 'ACTIVE'
GROUP BY Employee_name,Employee_loc
HAVING COUNT(*) > 1
)
GROUP BY EMPLOYEE_NAME, EMPLOYEE_ID

answered Apr 03 '13 at 10:28

Santhosh

1,771
1
15
25

Great.. thats cool. now for each employee_name , I have multiple rows. I just need to get the latest one by updated time? it is possible to get that? – Muthukumar Apr 03 '13 at 11:06
We need to Employee_status in the outer query, as it is again pulling employee with all status.. – Muthukumar Apr 03 '13 at 12:20

score 0 · Answer 3 · edited May 23 '17 at 12:17

0

You can also use partition by clause and select whichever columns you want to see irrespective of the columns you are using for aggregation.

A very short and simple explanation here - Oracle "Partition By" Keyword

edited May 23 '17 at 12:17

Community

1
1

answered Oct 01 '15 at 17:06

KLeonine

167
2
10

How to get all the columns, but have to group by only 2 columns in sql query

3 Answers3