How can I multiply an obtained value of this query for the output value of another query in SQL?

Question

I am not so into SQL and I am working on this query on a Microsoft SQL Server

SELECT
    tr.PolizzaID AS NumeroPolizza,
    pfr.FondoID AS CodiceFondo,
    '2016-09-30' AS dataRiferimentoPrezzo,
    SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) AS quote,
    SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) AS Controvalore
FROM
    TR_PrestazioneTotale tr WITH(nolock)

    ............................................................
    ............................................................
    DO SOME JOIN OPERATIONS
    ............................................................
    ............................................................

WHERE
    tr.PolizzaID = 1234567890
GROUP BY
    tr.PolizzaID,
    pfr.FondoID
HAVING 
    SUM(ISNULL(pre.impPre, 0) + ISNULL(riv.impRiv, 0)) <> 0

This query return always a single value.

As you can see one of the columns selected of the previous query is this one:

SUM(ISNULL(pre.impPre, 0) + ISNULL(riv.impRiv, 0)) AS Controvalore

that is a numeric value (something like 550058.8400).

My problem is that I have to multiply this

SUM(ISNULL(pre.impPre, 0) + ISNULL(riv.impRiv, 0))

value for the output of this other query:

select Aliquota 
from TirAliquotaRamoI 
where DataElaborazione = '2016-09-30 00:00:00'

Also this query return always a single value.

So basically, the Controvalore output field of the first query have to be the result of SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) multiplied by the Aliquota output value of the second query.

How can I do it?

Thanks

Answer 1

Since both return a single result, you can probably.. maybe get away with a subquery in your SELECT clause. I'm guessing by that aliquota name that this might work for your situation, but it really depends on the data underneath. At any rate, this is a good place to start.

SELECT
    tr.PolizzaID                                   AS NumeroPolizza,
    pfr.FondoID                                    AS CodiceFondo,
    '2016-09-30'                                   AS dataRiferimentoPrezzo,
    SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) AS quote,
    SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) * (select Aliquota from TirAliquotaRamoI where DataElaborazione = '2016-09-30 00:00:00') AS Controvalore
FROM
    TR_PrestazioneTotale tr WITH(nolock)

    ............................................................
    ............................................................
    DO SOME JOIN OPERATIONS
    ............................................................
    ............................................................

WHERE
    tr.PolizzaID = 1234567890
GROUP BY
tr.PolizzaID ,
pfr.FondoID
HAVING SUM(isnull(pre.impPre,0)+isnull(riv.impRiv,0)) <>0