Single column calculation in Postgresql

Asked

Viewed 1,473 times

0

How to make a calculation (example: subtraction) of summed values of the same column in Postgresql?

Example of query:

DROP TABLE if exists saldo;
CREATE TEMPORARY TABLE saldo AS select a.usuario
from table as a;

SELECT a.usuario, pontosP, pontosN FROM
(SELECT COALESCE(sum(b.pontos), (0))
FROM t1 b WHERE ((b.status = 1) AND (b.usuario = a.usuario))) AS pontosP,

(SELECT COALESCE(sum(c.pontos), (0))
FROM t2 c
WHERE ((c.status = 0) AND (c.id_usuario = a.id_usuario))) AS pontosN;

How to make a calculation using the return values, example: (pontosP - pontosN)?

In fact the select is not working properly because it does not find the input a although it exists in the temporary table.

postgresql
  • 1

    from table as a Is there such a syntax? I don’t know, do you have any reference to it? This would be to create an alias for the table name. And why do this. If you want to call the table a, why don’t you call her a? It’s a temporary table. There’s something I don’t understand?

1 answer

1

Your question is very confusing but it seems that you want to group by the user:

select
    usuario,
    coalesce(sum(b.pontos), 0) as pontosp,
    coalesce(sum(c.pontos), 0) as pontosn,
    coalesce(sum(b.pontos), 0) - coalesce(sum(c.pontos), 0) as "diferença"
from
    saldo a
    inner join
    t1 b using (usuario)
    inner join
    t2 c using (usuario)
where b.status = 1 and c.status = 0
group by usuario

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.