divide the results of 2 queries

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)

divide the results of 2 queries

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-01-03 : 00:01:01

Hi Can I divide the results of two separate queries in one query.
Example
Select count(*) from table1 where type = 'Prospect'
divide by
Select count(*) from table1 where city >''

TIA

ps - i tried to use a forward slash but it didn't work

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-03 : 00:16:55

select 1.0 * a.kount/b.kount from
(select count(*) as kount from table1 where type = 'Prospect') a
join
(select count(*) as kount from table1 where city >'') b on 1=1

elsasoft.org

doco
Yak Posting Veteran

77 Posts

Posted - 2008-01-03 : 00:21:11

[code]
select
( count( t1.*) / count( t2.* ) ) as quotient
from
table1 t1
join table2 t2
on t1.relate = t2.relate
where
t1.type = 'Prospect' and
t1.city not null
[/code]

Haven't tested this of course. But what I understand of what you gave this should work...

Education is what you have after you've forgotten everything you learned in school

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-01-03 : 00:37:02

Thanks for the super fast responses

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-03 : 02:03:55

quote:
Originally posted by doco

select
( count( t1.*)*1.0 / count( t2.* ) ) as quotient
from
table1 t1
join table2 t2
on t1.relate = t2.relate
where
t1.type = 'Prospect' and
t1.city not null

Haven't tested this of course. But what I understand of what you gave this should work...

Education is what you have after you've forgotten everything you learned in school

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-03 : 02:07:27

quote:
Originally posted by icw

Hi Can I divide the results of two separate queries in one query.
Example
Select count(*) from table1 where type = 'Prospect'
divide by
Select count(*) from table1 where city >''

TIA

ps - i tried to use a forward slash but it didn't work

Also try
Select
sum(case when type = 'Prospect' then 1 else 0 end)*1.0/sum(case when city>'' then 1 else 0 end)
from table1

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-03 : 02:13:18

Select
sum(case when type = 'Prospect' then 1.0 else 0.0 end) / sum(case when city > '' then 1.0 else 0.0 end)
from table1

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-03 : 03:18:22

quote:
Originally posted by Peso

Select
sum(case when type = 'Prospect' then 1.0 else 0.0 end) / sum(case when city > '' then 1.0 else 0.0 end)
from table1

E 12°55'05.25"
N 56°04'39.16"

or

Select
sum(case when type = 'Prospect' then 1.0 else 0.0 end) / sum(case when city > '' then 1 else 0 end)
from table1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources