arithmetic with floating tables

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

General SQL Server Forums

New to SQL Server Programming

arithmetic with floating tables

Author

Topic

moki311
Starting Member

1 Post

Posted - 2010-02-08 : 18:32:48

The following query does not work, but I feel I'm close. I want to divide the count of distinct pins that have disp='01' by the number of distinct pins.

select (select count(*) from (select distinct pin,disp from calls where disp='01') as t1)/(select count( distinct pin) from calls

Thanks in advance.

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 07:51:13

This perhaps?


SELECT Disp_01 / DistinctCount AS TheAnswer
FROM
(
    select SUM(CASE WHEN disp='01' THEN 1 ELSE 0 END) AS Disp_01
           COUNT(DISTINCT pins) AS DistinctCount
    from calls
) AS X

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 07:53:03

quote:

Originally posted by Kristen

This perhaps?


SELECT Disp_01 *1.0/ DistinctCount AS TheAnswer
FROM
(
    select SUM(CASE WHEN disp='01' THEN 1 ELSE 0 END) AS Disp_01
           COUNT(DISTINCT pins) AS DistinctCount
    from calls
) AS X

If you need decimal part also

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-10 : 08:19:34

As you see here
http://beyondrelational.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources