emerson lopes wrote:
>
>
> ...
> No entanto, ainda continuo com a duvida... No meu select, conforme abeixo,
> ele apenas me traz as contas analiticas.
>
> select a.conta, a.nome, sum(b.valor) as valor from plano_conta a left
> join
> lancamento_contabil b ON
> substr(a.conta,1,4) = substr(b.conta,1,4)
> group by a.conta, a.nome order by a.conta
>
>
Tente:
SELECT a.conta, a.nome, coalesce(b.valor_x,0) AS valor
FROM plano_conta a LEFT JOIN
(SELECT substr(conta,1,1) || '.00.000' AS conta_x, sum(valor) AS
valor_x
FROM lancamento_contabil
GROUP BY substr(conta,1,1)
UNION
SELECT substr(conta,1,4) || '.000' AS conta_x, sum(valor) AS valor_x
FROM lancamento_contabil
GROUP BY substr(conta,1,4)
UNION
SELECT conta AS conta_x, sum(valor) AS valor_x
FROM lancamento_contabil
GROUP BY conta) b
ON (a.conta = b.conta_x)
ORDER BY 1;
Osvaldo
PS.: Não testado
--
View this message in context: http://www.nabble.com/SQL-tp15238618p15276025.html
Sent from the PostgreSQL - Brasil mailing list archive at Nabble.com.
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
