I have a query as such:
SELECT disease_name, COUNT(DISTINCT id)
FROM disease_table
GROUP BY disease_name
where each disease_name has an associated identifier, and a disease may occur multiple times for the same identifier.
This works, BUT it takes roughly 7s to run.
If I run this query:
SELECT disease_name, COUNT(disease_name)
FROM disease_table
GROUP BY disease_name
it takes 321ms, BUT duplicate rows (same disease with same id) are counted more than once.
Is there a more efficient way to achieve the results of the first query in about the same time as the second using only SQL?
Table:
disease_name | id
------------ | -------------
dis_1 123
dis_1 104
dis_1 104
dis_32 123
dis_12 123
dis_12 115
Expected:
disease_name | count
------------ | -------------
dis_1 2
dis_32 1
dis_12 2
where dis_1 has 3 entries but is only counted twice because two of those 3 entries have the same id