Get RID of DISTINCT in SELECT

Hakan Axheim

8y993SQL

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?

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Tripathi8y ago
Accepted 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 Thomas8y ago
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 Axheim8y ago
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 Kumar8y ago
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 Axheim8y ago
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 Tripathi8y ago
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