Using select distinct with sum

I am not sure if what I want can be done but basically, I have a homework where I am supposed to come up with sql statements to filter through a database of a made up TCM company in any way. I wanted to filter branch to see which branch earns the most revenue.

but I am stuck here because I don’t know how to filter it.

select distinct b.branch_ID, t.fees
from treatment t,branch b, appointment a, appointment_treatment at
where t.treatment_ID = at.treatment_ID
and at.appt_ID = a.appt_ID
and b.branch_ID = a.branch_ID

The result is like so.

branch revenue

BR001   30.00   
BR001   45.00   
BR001   75.00   
BR002   28.00   
BR002   40.00   
BR002   60.00   
BR003   28.00   
BR003   60.00   
BR004   28.00   

However, what I want is:

branch (sum of) revenue for each branch

BR001   30.00   
BR002   45.00   
BR003   75.00   
BR004   28.00   
BR005   40.00   

but I can’t think of a way to make this work!

Answer

If I understood you problem correctly, you can use GROUP BY clause combined with SUM function to achieve what you want, like this:

select 
  b.branch_ID, sum(t.fees)
from 
  treatment t,branch b, appointment a, appointment_treatment at
where 
  t.treatment_ID = at.treatment_ID
  and at.appt_ID = a.appt_ID
  and b.branch_ID = a.branch_ID
group by
  b.branch_ID