Member Avatar for ashishsax

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
from
(select
  (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??

Edited by ashishsax because: to explain more clearly
  • 4 Contributors
  • 6 Replies
  • 328 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by ashishsax

Dani AI

Generated

Short answer: SQL does not let the WHERE clause "create" named values you can reference in the SELECT of the same statement. WHERE is a filter, not a place for assignments or reusable aliases; what you sketched (declare numerator/denominator inside WHERE and then use them in SELECT) is not valid SQL. The two single-statement patterns already suggested in the thread — the scalar-subselect approach noted by and the filtered-aggregation approach from — are the usual ways to produce a single-row numerator/denominator result.

Given the Veda DecisionPoint limitation (short SELECT/FROM fields, long WHERE), the practical options are:

  • Move the long logic into database objects you can call from a short SELECT (best when DDL is allowed). Encapsulate each complex predicate/count into a scalar function or small inline table-valued function, then call those functions from a compact SELECT. Example pattern:
CREATE FUNCTION dbo.fnNumerator() RETURNS INT AS
BEGIN
  RETURN (SELECT COUNT(1) FROM dbo.TableA WHERE /* long predicate here */);
END

SELECT CAST(dbo.fnNumerator() AS DECIMAL(18,6)) /
       NULLIF(dbo.fnDenominator(), 0) AS ratio;
  • If DDL is not permitted, compute the two counts in separate statements (or a short pre-step the tool may allow), store them in a tiny staging/temp table, then run a compact SELECT to compute the ratio. Many client tools can run two short queries and compute the division client-side — this avoids putting big SQL into SELECT/FROM.

A few practical notes: always guard against divide-by-zero with NULLIF; cast to a non-integer type for fractional ratios; prefer EXISTS or indexed predicates inside complex filters for performance; and if possible ask the vendor to expose a small "pre-SQL" or allow longer SELECT/FROM fields so the scalar-subselect or CROSS APPLY patterns are usable.

Recommended Answers

All 6 Replies

Member Avatar for Tess James 
select ((select count(1) from TableA where Condition1 ) /  (select count(1) from TableA where Condition2 )) as ratio
Member Avatar for urtrivedi 
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
Member Avatar for ashishsax

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.

Member Avatar for urtrivedi

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

Member Avatar for adam_k

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?

Edited by adam_k because: n/a
Member Avatar for ashishsax


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, networking, learning, and sharing knowledge.