CODEWITHSUNDEEP
sqloracle-database
Muthukumar
Muthukumar

Reputation: 113

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

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?

Upvotes: 2

Views: 19920

Answers (3)

KLeonine
KLeonine

Reputation: 187

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

Upvotes: 0

user1919238
user1919238

Reputation:

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.

Upvotes: 4

Santhosh
Santhosh

Reputation: 1791

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

Upvotes: 1

Related Questions