Hi All,
I'm pretty new to this and having difficulty with a query. Say I have Chemists and Doctors, a doctor writes a prescription and a chemist dispenses it. I want to work out percentage of scripts per doctor within each chemist.
For e.g.
Chemist1 dispensed prescriptions from doc1, doc2, doc3, doc4, doc5.
Doc1 had 10 prescriptions
Doc2 had 3 prescriptions
Doc3 had 22 prescriptions
Doc4 had 1 prescriptions
Doc5 had 5 prescriptions
So Chemist1 dispensed a total of 41 prescriptions.
So ultimately I want to say Doc1 had 24%, Doc2 had 7%, Doc3 had 54%, Doc4 had 2% and Doc5 had 12% of Chemist1 prescriptions.
I don't know how to get this all in one query. I can get the total prescriptions per chemist...
SELECT CHEMIST_ID, COUNT(PRESCRIPTION_ID) AS [TOTAL PER CHEMIST]
FROM PRESCRIPTION_TABLE A
INNER JOIN CHEMIST_TABLE C
ON A.CHEMIST_ID = C.CHEMIST_ID
WHERE PRESCRIPTION_TYPE = '2'
GROUP BY CHEMIST_ID
ORDER BY CHEMIST_ID
And the total prescriptions per doctor for each chemist...
SELECT A.CHEMIST_ID, C.CHEMIST_NAME, A.DOCTOR_ID, B.DOCTOR_NAME, COUNT(PRESCRIPTION_ID) AS [PRESCRIPTION COUNT]
FROM PRESCRIPTION_TABLE A
INNER JOIN DOCTOR_TABLE B
ON A.DOCTOR_ID = B.DOCTOR_ID
INNER JOIN CHEMIST_TABLE C
ON A.CHEMIST_ID = C.CHEMIST_ID
WHERE PRESCRIPTION_TYPE = 2
GROUP BY A.CHEMIST_ID, C.CHEMIST_NAME, A.DOCTOR_ID, B.DOCTOR_NAME
ORDER BY CHEMIST_ID
I want these together somehow so I can then work out the percentage...
([PRESCRIPTION COUNT]/[TOTAL PER CHEMIST]) * 100
Any help is greatly appreciated.
:)
Grouping Records and calculating totals
Posted - 10/31/2012 : 05:27:19
