CODEWITHSUNDEEP
pythonsqlpython-3.xsqlite
Ayoub Rasta
Ayoub Rasta

Reputation: 17

Python3 Sqlite3: how to count the Duplicates items but with omitting a substring from it

I have a table that contains a column called Patient as shown below:

Patient
---------
Perez, MD
Perez, MD
Perez, MD
Perez
Perez
Ayoub, MD
Ayoub
.
.

and so on ....

I added this query

query = "SELECT Patient, COUNT(*) FROM Table GROUP BY Patient ORDER BY COUNT(*) DESC"

which returns this result:

[('Perez, MD', 3), ('Perez', 2), ('Ayoub, MD', 1), ('Ayoub', 1), ......]

but I want the proper way to get this result?

[('Perez, MD', 5), ('Ayoub, MD', 2), ......]

or this:

[('Perez', 5), ('Ayoub', 2), ......]

Upvotes: 0

Views: 63

Answers (1)

forpas
forpas

Reputation: 164099

Check if the patient's name contains , and if it does extract the left part to use it in the GROUP BY clause: 

SELECT 
  CASE 
    WHEN INSTR(Patient, ',') = 0 THEN Patient
    ELSE SUBSTR(Patient, 1, INSTR(Patient, ',') - 1)
  END Name, 
  COUNT(*) counter 
FROM tablename 
GROUP BY Name 
ORDER BY COUNT(*) DESC

Or remove any occurrence of ', MD':

SELECT 
  REPLACE(Patient, ', MD', '') Name, 
  COUNT(*) counter 
FROM tablename 
GROUP BY Name 
ORDER BY COUNT(*) DESC;

See the demo.
Results:

| Name  | counter |
| ----- | ------- |
| Perez | 5       |
| Ayoub | 2       |

Upvotes: 1

Related Questions