CODEWITHSUNDEEP
sql
RDPD
RDPD

Reputation: 565

SQL Query Clarification

I have two tables doctor and billing. I wish to view the dname,doctorid and sum(fees) of doctors treating more than one patient.

doctor table columns are as follows:

doctorid,dname

billing table columns are as follows:

billid,patientid,doctorid,fees

I tried the following code. But got an error stating SQL cmd not properly ended:

SELECT d.dname,b.doctorid,Sum(b.fees)as "TotalFees" FROM billing b,doctor d ON b.doctorid=d.doctorid
GROUP BY b.doctorid HAVING count(b.patientid)>1;

where have i gone wrong with the syntax? Thanks...

Upvotes: 1

Views: 91

Answers (4)

Xavi López
Xavi López

Reputation: 27880

You're missing the INNER JOIN keyword, and you also need to GROUP BY all the non-aggregate columns you're using in the SELECT clause (GROUP BY d.dname, b.doctorid in this case).

Also, it might be a typo, but you haven't got a space between Sum(b.fees) and as: 

SELECT d.dname, b.doctorid, Sum(b.fees) as "TotalFees" 
FROM billing b INNER JOIN doctor d ON b.doctorid=d.doctorid
GROUP BY d.dname, b.doctorid HAVING count(b.patientid)>1;

Remember using comma separated table names in a FROM clause will perform a cartesian product, which will be a performance killer depending on your RDBM's optimizer. In case you'd like to use it, remember to put its join conditions in the WHERE clause (the ON clause is used along with the JOIN syntax): 

SELECT d.dname, b.doctorid, Sum(b.fees) as "TotalFees" 
FROM billing b, doctor d 
WHERE b.doctorid = d.doctorid
GROUP BY d.dname, b.doctorid HAVING count(b.patientid)>1;

Upvotes: 1

Rahul
Rahul

Reputation: 5636

You have to mentioned into your query about which join you want to use,you have to change your query like

SELECT d.dname, b.doctorid, Sum(b.fees) as "TotalFees" 
FROM billing b inner join doctor d ON b.doctorid=d.doctorid
GROUP BY d.dname, b.doctorid HAVING count(b.patientid)>1;

Check Join

Upvotes: 1

Krishna Rani Sahoo
Krishna Rani Sahoo

Reputation: 1539

Try this

SELECT d.dname,b.doctorid,Sum(b.fees)as "TotalFees" FROM billing b inner join doctor d ON b.doctorid=d.doctorid
GROUP BY d.dname,b.doctorid HAVING count(b.patientid)>1;

Upvotes: 1

Skyp
Skyp

Reputation: 1197

FROM billing b,doctor d ON b.doctorid=d.doctorid should be FROM billing b inner join doctor d ON b.doctorid=d.doctorid

Upvotes: 1

Related Questions