Make single-column calculation in PostgreSQL

Question

Make single-column calculation in PostgreSQL

Navigation

#1 by (1 votes)

0

How can I make a calculation (example: subtraction) of summed values of the same column in PostgreSQL?

Query example:

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) ?

Actually select is not working properly because it does not find the a entry even though it exists in the temporary table.

postgresql

asked by anonymous 26.11.2014 / 15:50

1 answer

How to display form of a BD Access directly on a website? Problems with the text listview above the icons

score 1 · Answer 1

Your question is very confusing but it looks like 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