Division of distinct counts - SQL Server Forums

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

Development Tools

ASP.NET

Division of distinct counts

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2008-01-10 : 01:22:34

select top 10 Count(Distinct medid),Count(Distinct patientid), Count(Distinct medid) /Count(Distinct patientid) from medications group by bdrugid
I am not getting the exact output for,Count(Distinct MedID)/Count(Distinct PatientID) as its truncating the floating value

Highest Count shoud be the first. but i am not getting as expected

(Distinct medid) (Distinct PatientID) division -->Column names
1 1 1-->Highest shld be first
5 2 2-->truncatin 5/2=2.5
3 3 1
1 1 1
3 2 1
3 3 1
1 1 1
1 1 1
1 1 1
2 1 2

How to get the exact division with a single query without writing store proc.

Thanks and Regards,
Swati Jain

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-10 : 03:13:04

Use

Count(Distinct MedID)*1.0/Count(Distinct PatientID)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources