SQL - creating new columns by making calculations on the result of a join of 2 tables-CodePudding

I have 2 select queries that I joined like this:

select * from
    (
    select sum(checkedcrates.checkedcrates) as sumA, checkedcrates.paramdescription from 
    (another query...) 
    as checkedcrates 
    group by checkedcrates.paramdescription
    )
as A
Join
    (select sum(cratecnt.cnt) as sumB, cratecnt.paramdescription from 
    (yet another query... ) 
    as cratecnt 
    group by cratecnt.paramdescription)
as B
on a.paramdescription = b.paramdescription order by B.sum desc

The result of this query is this:

sumA     paramdescription     sumB     paramdescription
----------------------------------------------------------
1779     bottom               10       bottom
1779     totalarea            8        totalarea
1779     innerwalls           7        innerwalls
1779     risperror            1        risperror
1779     outerwalls           0        outerwalls
1779     clipserror           0        clipserror
1779     totalnumberdefects   0        totalnumberdefects

So far so good. Now I would need to add a fifth column, as the result of sumB/sumA. I know how to do it on a simple query, but I do not know how to add it after the join, using the data from two different select queries.

CodePudding user response：

Simply add b.sumB / a.sumA to the select list:

select a.*, b.*, b.sumB / a.sumA from
...

Perhaps you want to do b.sumB * 1.0 / a.sumA, to avoid integer division (depending on dbms used.)