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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
17689 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
17689 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  
 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.07 seconds. Powered By: Snitz Forums 2000