Add a column where its values are already the result of another sum

Question

I have 2 tables in a database, one contains the purchase orders, another contains the items of these purchase orders, this data is obtained from an import from another system so I do not have foreign key conveying the tables, but I have information that I can uni-las no select.

Purchase Orders:

Items of Purchase orders: Note: These are just some of the records I have in the database to use as an example.

The select I make is this:

SELECT sc.empresa, sc.cod_autorizante, sc.codigo_ordem_compra, ( 
    SELECT SUM(ioc.valor_total) as soma 
    FROM itens_ordens_compras ioc 
    WHERE ioc.codigo_empresa = sc.empresa 
    and ioc.cod_ordem_compra = sc.codigo_ordem_compra ) as valor_t 
from solicitacao_compras sc 
where sc.cod_autorizante = 222 
and sc.data_aprovacao like '%/10/2020' 
GROUP by sc.codigo_ordem_compra;

Note. 2: I do with the GROUP by sc.codigo_ordem_purchase because without it I have records that end up repeating themselves.

And I can get this result:

My question and need is, I can give a select next to this only by adding a column valor_t where would bring me in the case of this example the value R$203,034.66, the other information would not need to appear, only this final value same.

From now on, thank you.

Answer 1

Good afternoon,

I have no experience with Mysql, but if the version you are using allows you to use CTE (Common Table Expressions), you can try it in a similar way to the one below:

with CTE_Origem as
(
    -- consulta inicial
)

select sum(soma) as soma from CTE_Origem

If by chance it is not possible to use CTE, you can try with a derived table:

select sum(soma) as soma
from
(
    -- consulta inicial
) as t

Edit: complete example with a derived table:

select sum(t.valor_t) as soma
from
(
  SELECT sc.empresa, sc.cod_autorizante, sc.codigo_ordem_compra, ( 
    SELECT SUM(ioc.valor_total) as soma 
    FROM itens_ordens_compras ioc 
    WHERE ioc.codigo_empresa = sc.empresa 
    and ioc.cod_ordem_compra = sc.codigo_ordem_compra) as valor_t 
  from solicitacao_compras sc 
  where sc.cod_autorizante = 222 
  and sc.data_aprovacao like '%/10/2020' 
  GROUP by sc.codigo_ordem_compra
) as t

I hope it helps