SQL Grouping Query Help

[methodmano]

I have a DB that is keeping track of refills of patient's drugs. In one table I have the number of refills and an ID (ID,refills - other fields omitted) that corresponds to another table with the drug information (ID,name,dosage,price). Ex. (2,8) -> (2,drugA,dosageA,priceA) where 2 is the ID corresponding to the other table and 8 is the number of refills. A simple join gives me something like the following, where each row for the same drug is a different patient:

2 drugA dosageA priceA
3 drugA dosageA priceA
1 drugA dosageA priceA
7 drugB dosageB priceB
2 drugB dosageB priceB
etc...

I'm making this into a report so what I would like, using the example above is:
6 drugA dosageA priceA total(6*priceA)
9 drugB dosageB priceB total(9*priceB)
etc...
total(6*priceA + 9*priceB + etc...)

I'm struggling with what SQL query would give me this. I'm pretty sure it's possible, but the combinations I've tried so far didn't yield the results I was looking for. Thanks a lot in advance for any help/suggestions!

 

[PHV]

Something like this ?
SELECT Sum(P.refills) AS CountOfRefills, D.name, D.dosage, D.price, Sum(P.refills)*D.price AS Total
FROM tblPatients P INNER JOIN tblDrugInfos ON P.ID = D.ID
GROUP BY D.name, D.dosage, D.price;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[methodmano]

Thanks a lot! I knew I was missing something simple. Thanks a lot for your help.