CODEWITHSUNDEEP
sqlsql-serversubquery
Pat
Pat

Reputation: 113

Sql - how to calculate 2 separate sub queries?

I do not have the best understanding of SQL, doing my best to learn. I have built two separate queries, and I am looking to take the result from query 1 and divide it by query 2.

Query 1 (Result is 114)

select count(*) as Numerator

from (

SELECT   *,  
           DATEDIFF(SECOND, xx, yy) AS SecondDiff, 
           DATEDIFF(DAY, xx, yy) AS DayDiff

FROM database1.dbo.tbl1

WHERE  month(datecompleted) = month(dateadd(month,-1,current_timestamp))
       and year(datecompleted) = year(dateadd(month,-1,current_timestamp))
       and DATEDIFF(DAY, xx,yy) <= 15

)t1

Query 2 (result is 131)

select count(*) as Denominator

from(

SELECT   *,  
           DATEDIFF(SECOND, xx, yy) AS SecondDiff, 
           DATEDIFF(DAY, xx, yy) AS DayDiff

FROM database1.dbo.tbl1

WHERE  month(datecompleted) = month(dateadd(month,-1,current_timestamp))
       and year(datecompleted) = year(dateadd(month,-1,current_timestamp))


)t2

Now how do I take the results from query 1 and divide it by query 2 and have the end result be (Column name: Total_X)? I have tried to use union, cross join, cross apply but I cannot seem to get a working query!

Every tip and advice is greatly appreciated

Thanks

Upvotes: 1

Views: 441

Answers (4)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a CROSS JOIN BETWEEN the two query eg:

  select TT1.Numerator/ TT2.Denominator
  from (
    select count(*) as Numerator

  from (

  SELECT   *,  
             DATEDIFF(SECOND, xx, yy) AS SecondDiff, 
             DATEDIFF(DAY, xx, yy) AS DayDiff

  FROM database1.dbo.tbl1

  WHERE  month(datecompleted) = month(dateadd(month,-1,current_timestamp))
         and year(datecompleted) = year(dateadd(month,-1,current_timestamp))
         and DATEDIFF(DAY, xx,yy) <= 15

  )t1) TT1 
  CROSS JOIN ( 

  select count(*) as Denominator

  from(

  SELECT   *,  
             DATEDIFF(SECOND, xx, yy) AS SecondDiff, 
             DATEDIFF(DAY, xx, yy) AS DayDiff

  FROM database1.dbo.tbl1

  WHERE  month(datecompleted) = month(dateadd(month,-1,current_timestamp))
         and year(datecompleted) = year(dateadd(month,-1,current_timestamp))


  )t2 ) TT2

could be related to integer division try a simple cast 

select cast(TT1.Numerator AS DECIMAL(9,2))/CAST( TT2.Denominator AS DECIMAL(9.2))
  from (
    select count(*) as Numerator

  from (

  SELECT   *,  
             DATEDIFF(SECOND, xx, yy) AS SecondDiff, 
             DATEDIFF(DAY, xx, yy) AS DayDiff

  FROM database1.dbo.tbl1

  WHERE  month(datecompleted) = month(dateadd(month,-1,current_timestamp))
         and year(datecompleted) = year(dateadd(month,-1,current_timestamp))
         and DATEDIFF(DAY, xx,yy) <= 15

  )t1) TT1 
  CROSS JOIN ( 

  select count(*) as Denominator

  from(

  SELECT   *,  
             DATEDIFF(SECOND, xx, yy) AS SecondDiff, 
             DATEDIFF(DAY, xx, yy) AS DayDiff

  FROM database1.dbo.tbl1

  WHERE  month(datecompleted) = month(dateadd(month,-1,current_timestamp))
         and year(datecompleted) = year(dateadd(month,-1,current_timestamp))


  )t2 ) TT2

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

Another way is with computed table expressions.

with numerator
as (
select count(*) n1
from someTable
) 
, denominator as
(select count(*) n2
from anotherTable)

select n1 / n2
from numerator, denominator
where n2 > 0

Note that 

from numerator, denominator

is effectively a cross join, as suggested by @scaisEdge

Also note that your actual queries look to be more complicated than necessary.

Upvotes: 0

Avani
Avani

Reputation: 215

  1. You can convert rows to column and divide it. Assuming your first query is select 1 value, 'nm' type from dual; second query is select 2 value, 'dm' type from dual

Convert rwos to column and divide

select sum(decode(type,'nm',value)) numerator,
sum(decode(type,'dm',value)) denominator , sum(decode(type,'nm',value))/sum(decode(type,'dm',value)) divided_value from (select 1 value, 'nm' type from dual union
select 2 value, 'dm' type from dual) group by 1,2

or directly

  select 1/(select 2 from dual) from dual;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

I think you are looking for this:

SELECT AVG(CASE WHEN DATEDIFF(DAY, xx,yy) <= 15 THEN 1.0 ELSE 0 END)
FROM database1.dbo.tbl1
WHERE month(datecompleted) = month(dateadd(month,-1,current_timestamp)) AND
      year(datecompleted) = year(dateadd(month,-1,current_timestamp));

You can also simplify the WHERE clause to:

SELECT AVG(CASE WHEN DATEDIFF(DAY, xx,yy) <= 15 THEN 1.0 ELSE 0 END)
FROM database1.dbo.tbl1
WHERE datecompleted < DATEADD(day, 1 - DAY(current_timestamp), CONVERT(DATE, datecompleted)) AND
      datecompleted >= DATEADD(month, 1, DATEADD(day, 1 - DAY(current_timestamp), CONVERT(DATE, datecompleted))) AND

This version can make use of an index on datecompleted.

Upvotes: 1

Related Questions