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

Question

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

user330315 · Accepted Answer · 2022-03-18 12:19:17Z

1

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

answered Mar 18, 2022 at 12:19

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

João Pedro Reis Silva Over a year ago

Better than I could even imagine, I didn't know the filter, but I didn't understand why it's necessary to use the sentence "WHERE exercicio_orcamento IN (2020, 2021)" after "from". Considering that the records already been filtered after each SUM() And just for curiosity, to learn more one thing. Is it possible to do that like I was trying? Assigning to a variable the output of a query? If so, how could I do that?

user330315 Over a year ago

@JoãoPedroReisSilva: it's mainly a performance optimization so it doesn't need to read all years just to calculate the result for two.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related