SQL: join on foreign key between two tables and return only count of items; not actual items

I know there’s lots of questions similar to this out there. I’m writing my first SQL query; and I’ve been looking through SO but I can’t find out specifically what I’m doing wrong, because to me, they look the same as the answers here.

I have three tables:

• disease: has columns: id, name, stage (info about different diseases)
• drug: has columns: id, name, other info about the drugs (info about different drugs)
• disease_drug_link: has foreign key: disease_id (from disease), drug_id (from drug) and other info (info about which drugs to use on which disease).

I want to return, for each disease, the number of drugs for that disease so that i can make a graph; this info is linked together in the disease_drug_link table.

I wrote this as a draft, but I’m just struggling with it:

SELECT DP.*, PA.*
FROM disease as DP
LEFT JOIN disease_drug_link as PA
ON DP.id = PA.drug_id
#group by PA.drug_id #### (conn=773299) Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column which is not functionally dependent on columns in GROUP BY clause
#group by DP.drug_id #### (conn=773299) You have an error in your SQL syntax
#group by PA.id ### (conn=773299) You have an error in your SQL syntax
#group by DP.id ### (conn=773299) You have an error in your SQL syntax
limit 10

Can someone demonstrate the correct way to do this?

Answer

Use GROUP BY:

SELECT DP.ID, COUNT(PA.drug_id) as num_drugs
FROM disease DP LEFT JOIN
     disease_drug_link as PA
     ON DP.id = PA.drug_id
GROUP BY DP.ID;

If you want additional columns from DP, then in most databases you would add them to the SELECT and GROUP BY:

SELECT DP.ID, DP.NAME, COUNT(PA.drug_id) as num_drugs
FROM disease DP LEFT JOIN
     disease_drug_link as PA
     ON DP.id = PA.drug_id
GROUP BY DP.ID, DP.NAME;