Hakan Axheim

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?
 

Answers (6)

0
Manas Tripathi

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

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

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

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

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

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