OK, here is what I want to really do:

I need to write a single SQL query to do the following:
select count(1) from TableA where [condition1]/ select count(1) from TableA where [condition2]

One way of doing it is:

select (t.numerator/t.denominator) as ratio
  (select count(1) from TableA where [condition1]) as numerator,
  (select count(1) from TableA where [condition2]) as denominator
 ) t

However, the tool into which I have to feed this query does not like such a long 'from' clause. Also, the option of making a view is not permissable.
So I want to find the numerator and denominator in the 'where' clause and divide them in the select clause.
Something like this:

select numerator/ denominator
from TableA
where numerator = (select query using TableA with some conditions)
and denominator = (select query using TableA with another set of conditions)

Any ideas??

Recommended Answers

All 6 Replies

select ((select count(1) from TableA where Condition1 ) /  (select count(1) from TableA where Condition2 )) as ratio
select sum(case when co1='conditin1' then 1 else null end)/sum(case when co2='conditin2' then 1 else null end) as ratio from TableA

Thanks so much Tessy7 and Urtrivedi. Both of your solutions are good however, I want to do the numerator and denominator calculation in the where clause. I have a client tool which doesnt like big 'Select' and 'From' clauses.It breaks every query into select, from and where fields, which it stores into a database.

Select field is varchar(250)
from field is varchar(250)
where field is of type text, so I can put long sql here..

Hence, I cannot put long sql blocks in either 'select' or 'from' clause. Need to find a way to push the numerator and denominator calculation in the where clause.

the solution i have provide is not execedding 250 characters. if you want to calculate, you have to do in select part

I was in a similar situation a few years back and figured out that the tool I was using only concatenated the 3 fields when it was running the query. My solution was to write the query just how I liked it and then split it to fit inside the fields. Of course the tool didn't support this, but Query Analyzer didn't have a problem updating the fields.

PS: What "client tool" are you refering to?

The conditions I have for finding the numerator and denominator are complex queries themselves and if put in the 'select' or 'from' clause, easily exceed 700 chars.

I'm using Veda DecisionPoint Builder tool. I understand what you mean and maybe will have to consider similar alternatives I guess!

Thanks to everyone who replied. :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.