CODEWITHSUNDEEP
sql
winchmore
winchmore

Reputation: 37

combining two query results into one and using the results to compute another column

Query 1 returns one number AveSpace i.e. 100

select avg(spaceusage) as AveSpace
from
(
select top (5) *  from Employee
order by Date desc
) as s

Query 2 returns one number TotalSpace i.e. 1000

select top(1) TotalSpace from SpaceTable
order by date

I want to combines the results into one dataset and add a third column unitSpace that calculates the result from Query 2/ Query 1 i.e 1000/100 =10

output 3 columns

[AveSpace] [TotalSpace] [unitSpace]

100          1000          10

Upvotes: 0

Views: 86

Answers (2)

Isaac Hildebrandt
Isaac Hildebrandt

Reputation: 1026

Treat each individual query as a subquery then compute the results:

SELECT a.AveSpace, b.TotalSpace, (CAST(a.AveSpace AS float) / CAST(b.TotalSpace AS float)) as UnitSpace
FROM 
    (
        SELECT TOP 1 AVG(spaceusage) as AveSpace FROM (SELECT TOP 5 * FROM Employee ORDER BY date DESC)
    ) a,
    (
         SELECT TOP 1 TotalSpace FROM SpaceTable ORDER BY date
    ) b

Upvotes: 2

Mike
Mike

Reputation: 1

Declare @TotalSpace FLOAT

SELECT TOP 1 @TotalSpace = TotalSpace from SpaceTable order by date

SELECT select avg(spaceusage) as AveSpace, @TotalSpace AS TotalSpace, @TotalSpace/avg(spaceusage) AS unitSpace from ( select top (5) * from Employee order by Date desc ) as s

Upvotes: 0

Related Questions