SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Grouping Records and calculating totals
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLConfusesMe
Starting Member

4 Posts

Posted - 10/31/2012 :  05:27:19  Show Profile  Reply with Quote
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)

Singapore
17645 Posts

Posted - 10/31/2012 :  05:31:43  Show Profile  Reply with Quote
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



KH
Time is always against us

Go to Top of Page

SQLConfusesMe
Starting Member

4 Posts

Posted - 10/31/2012 :  06:15:13  Show Profile  Reply with Quote
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 :)
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17645 Posts

Posted - 10/31/2012 :  09:59:07  Show Profile  Reply with Quote
can you post some sample data and the expected result ?



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000