Grouping Records and calculating totals

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Grouping Records and calculating totals

Author

Topic

SQLConfusesMe
Starting Member

4 Posts

Posted - 2012-10-31 : 05:27:19

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.
:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-10-31 : 05:31:43

[code]SELECT A.CHEMIST_ID, C.CHEMIST_NAME, A.DOCTOR_ID, B.DOCTOR_NAME, COUNT(PRESCRIPTION_ID) AS [PRESCRIPTION COUNT],
COUNT(PRESCRIPTION_ID) / COUNT(PRESCRIPTION_ID) OVER (PARTITION BY A.CHEMIST_ID)
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[/code]

KH
[spoiler]Time is always against us[/spoiler]

SQLConfusesMe
Starting Member

4 Posts

Posted - 2012-10-31 : 06:15:13

Hi khtan,

I tried you suggestion but it doesn't seem to do what I want. The [Prescription Count] for every record returns the value 1, rather than grouping and counting the amount of Doctors prescriptions.
E.g.

Chemist Doctor Prescription Count
Chemist1 Doc1 1
Chemist1 Doc2 1
Chemist1 Doc3 1
Chemist1 Doc3 1
Chemist1 Doc4 1
Chemist1 Doc5 1
Chemist1 Doc5 1

Can I group them so Doc3 would have prescription count of 2 and Doc5 would have prescription count of 2?

Thanks in advance :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-10-31 : 09:59:07

can you post some sample data and the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources