Query that returns sum where states are equal to X and other clusters

Question

I have a table where contains the sale value of some states of Brazil, I would like to perform a query that returns me the sale value of the states of SP and RJ, and for the other states he returns me the sum of what is different from that.

To illustrate that I have this table

State | Sales

SP | 20

RJ | 30

GO | 50

PA | 30

TO | 5

I would like the consultation to return to me SP (20), RJ (30), Other (85).

I’m very beginner in SQL and I’m having a little trouble

With this code I can bring all separate sales but I can’t group those that are different from SP and RJ

SELECT ESTADO, SUM(VALOR_VENDA) FROM vendas GROUP BY ESTADO ORDER BY ESTADO ASC;

Someone could give me a light?

Answer 1

One possibility is to use CASE / WHEN:

SELECT SUM(CASE WHEN estado = 'SP' THEN Vendas ELSE 0) AS SP,
       SUM(CASE WHEN estado = 'RJ' THEN Vendas ELSE 0) AS RJ,
       SUM(CASE WHEN estado <> 'SP' AND estado <> 'RJ' THEN Vendas ELSE 0) AS OUTROS
FROM sua_tabela;

A row with three columns with the desired values.

Answer 2

Or

SELECT (CASE WHEN ESTADO NOT IN ('RJ','SP') THEN 'OUTROS' ELSE ESTADO END) ESTADO, 
       SUM(VALOR_VENDA)
 FROM vendas 
GROUP BY (CASE WHEN ESTADO NOT IN ('RJ','SP') THEN 'OUTROS' ELSE ESTADO END) 
ORDER BY (CASE WHEN ESTADO NOT IN ('RJ','SP') THEN 'OUTROS' ELSE ESTADO END) ASC