TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Hakan Axheim
NA
201
33.3k
Get RID of DISTINCT in SELECT
Mar 20 2018 4:14 PM
Hi,
I have a SQL query that looks like this:
SELECT department, count(distinct person_id) AS NumberOfPersons
FROM person
GROUP BY department
This query wil show me how many persons that are employed on departments. A person with same person_id can exist on several records in the person table therefore I have used distinct before count.
Because of performance issue I did not want to use DISTINCT!
How can I rewrite this SQL whith no DISTINCT in the SELECT statement?
Reply
Answers (
6
)
0
Manas Tripathi
0
272
30
Mar 23 2018 12:31 AM
WITH CTE AS
(
SELECT DEPARTMENT, COUNT(PERSONID) 'PersonId' FROM PERSON
GROUP BY DEPARTMENT,PERSONID
)
Select Department, Count(PersonId) FROM CTE Group By Department
Accepted Answer
3
Joseph Thomas
793
1k
368.1k
Mar 22 2018 2:22 AM
Hi you can use multiple columns in group by....
Try this..
SELECT COUNT(person_id) FROM ( select department, person_id from test group by person_id, department )tbl GROUP by department
1
Hakan Axheim
0
201
33.3k
Mar 21 2018 4:18 AM
Maybee it's easier for you too understande my problem if I give you som data. See an extract from the table I select my data from:
Department Person ID
111 010101-9009
111 010101-902B
111 111111-KOI1
111 111111-KOI1
111 121212-900Y
1234 232323-A010
1234 343434-A020
The result I except from the SQL statement that select data from the table above is:
4 different kind of person ID for department ID 111 (for department 111 I have two rows with person ID 111111-KOI1)
2 different kind of person ID for department ID 1234
1
Manish Kumar
0
1.4k
8.4k
Mar 21 2018 3:07 AM
Try GROUP BY as a subquery and COUNT() from outside query. It would achieve same result.
SELECT COUNT(*)
FROM
(
SELECT Col1
FROM Table
GROUP BY Col1
) tbl
1
Hakan Axheim
0
201
33.3k
Mar 21 2018 12:17 AM
It Will not be correct because i just want to count the distinct but i don’t want to use distinct keyword. By Department the same person_id can exist on several rows.
1
Manas Tripathi
0
272
30
Mar 20 2018 7:30 PM
As you want number of employees in each department and you have already grouped it by dapartment. You can just remove distinct from query.
SELECT DEPARTMENT, COUNT(PERSON_ID) AS NUMBEROFPERSONS
FROM PERSON
GROUP BY DEPARTMENT
I have one doubt for SSIS please help me SSIS experts
Merge Statement