Get RID of DISTINCT in SELECT

Question

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?

Manas Tripathi · Answer

WITH CTE AS ( SELECT DEPARTMENT, COUNT(PERSONID) 'PersonId' FROM PERSON GROUP BY DEPARTMENT,PERSONID ) Select Department, Count(PersonId) FROM CTE Group By Department

Joseph Thomas · Answer

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

Hakan Axheim · Answer

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

Manish Kumar · Answer

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

Hakan Axheim · Answer

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.

Manas Tripathi · Answer

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