How to assign the result of a query to a variable? (query optimization)-CodePudding

I have a table with the following columns: codigo_receita VARCHAR, valor_orcamento NUMERIC(30,2) and exercicio_orcamento INTEGER.

I created a query that works, but I would like to optimize it, without repeating code.

How do I assign the value of each query to a variable?

In order to get something like this:

SELECT
(query_1 - query_2) / query_2
AS changes

The query I created:

SELECT
((SELECT
    SUM(valor_orcamento)
FROM orcamento
WHERE exercicio_orcamento = 2021
GROUP BY exercicio_orcamento)
-
(SELECT
    SUM(valor_orcamento)
FROM orcamento
WHERE exercicio_orcamento = 2020
GROUP BY exercicio_orcamento))
/
(SELECT
    SUM(valor_orcamento)
FROM orcamento
WHERE exercicio_orcamento = 2020
GROUP BY exercicio_orcamento) AS change

CodePudding user response：

You can do that in a single statement. You probably want to cast the result of the sum() to numeric otherwise the division would be an integer division:

SELECT (
         SUM(valor_orcamento) filter (WHERE exercicio_orcamento = 2021) 
         - 
         SUM(valor_orcamento) filter (WHERE exercicio_orcamento = 2020)
       )::numeric
       / 
       SUM(valor_orcamento) filter (WHERE exercicio_orcamento = 2020)
FROM orcamento
WHERE exercicio_orcamento IN (2020, 2021)
GROUP BY exercicio_orcamento