Postgresql COUNT function does not work

Question

The problem is simple but I can not understand where I am missing, I need to check the amount of results of the following sql:

SELECT 

f.id,sum(p.valor) as valor_fatura, f.valor_desconto as desconto, 
(f.numero || '/' || f.ano_fatura ) as numero,
(sum(p.valor) + f.valor_desconto + f.valor_acrescimo)as 
valor,f.valor_acrescimo as acrescimo, f.emissao_em,
f.vence_em, f.situacao, f.juros_acrescimo, hb.dt_hr_inclusao::date AS 
vencimento_ultimo_recalculo,
f.multa_acrescimo, f.is_gera_boleto, f.qtd_taxa_recalculo, 
f.valor_taxa_recalculo, f.is_confianca,
(sumid(p.is_baixado) = sumid(true)) as is_baixado 

FROM parcels p 
JOIN sales s ON (p.sale_id = s.id)
JOIN faturas f ON (p.fatura_cobranca_id = f.id)

LEFT JOIN (SELECT (array_agg(dt_hr_inclusao order by dt_hr_inclusao 
desc))[1] as dt_hr_inclusao,
(array_agg(cod_mov_remessa_id order by dt_hr_inclusao desc))[1] as 
cod_mov_remessa_id,
hb.fatura_id FROM historico_remessa_boleto hb GROUP BY hb.fatura_id 
) hb ON (f.id = hb.fatura_id and hb.cod_mov_remessa_id = 6)

WHERE p.is_baixado = false AND f.vence_em is not null AND is_gera_boleto 
AND consignatario_id = 6512 

GROUP BY f.id, f.numero, f.ano_fatura, f.emissao_em, f.vence_em, 
f.situacao, f.juros_acrescimo, f.multa_acrescimo, f.is_gera_boleto, 
f.valor_desconto, f.valor_acrescimo, hb.dt_hr_inclusao::date 

The problem is that when I run this sql it brings me 6 results, to know the amount now I would do it :

SELECT 
count(p.id)
FROM parcels p 
JOIN sales s ON (p.sale_id = s.id)
JOIN faturas f ON (p.fatura_cobranca_id = f.id)
LEFT JOIN (SELECT (array_agg(dt_hr_inclusao order by dt_hr_inclusao 
desc))[1] as dt_hr_inclusao,
(array_agg(cod_mov_remessa_id order by dt_hr_inclusao desc))[1] as 
cod_mov_remessa_id,
hb.fatura_id FROM historico_remessa_boleto hb GROUP BY hb.fatura_id 
) hb ON (f.id = hb.fatura_id and hb.cod_mov_remessa_id = 6)

WHERE p.is_baixado = false AND f.vence_em is not null AND is_gera_boleto 
AND consignatario_id = 6512 

Now it returns me 11 results on Count. I’m not good at sql my focus is front-end so I’m kind of lost from where I can is missing, in case someone needs some information that I didn’t just ask in the comments.

---

When I leave the query with the group by it returns me 6 rows of results where I can only have a result with the total amount.

Answer 1

By the way, when you leave with the group by returns 6 lines, with the result 6 in each of them, correct ? you can use the distinct for that.

ps. Try to match the code to help read.

SELECT
count(DISTINCT p.id)
FROM parcels p 
JOIN sales s ON (p.sale_id = s.id)
JOIN faturas f ON (p.fatura_cobranca_id = f.id)
LEFT JOIN (SELECT (array_agg(dt_hr_inclusao order by dt_hr_inclusao 
desc))[1] as dt_hr_inclusao,
(array_agg(cod_mov_remessa_id order by dt_hr_inclusao desc))[1] as 
cod_mov_remessa_id,
hb.fatura_id FROM historico_remessa_boleto hb GROUP BY hb.fatura_id 
) hb ON (f.id = hb.fatura_id and hb.cod_mov_remessa_id = 6)

WHERE p.is_baixado = false AND f.vence_em is not null AND is_gera_boleto 
AND consignatario_id = 6512 

GROUP BY f.id, f.numero, f.ano_fatura, f.emissao_em, f.vence_em, 
f.situacao, f.juros_acrescimo, f.multa_acrescimo, f.is_gera_boleto, 
f.valor_desconto, f.valor_acrescimo, hb.dt_hr_inclusao::date 

Answer 2

In the first query you have a grouping (GROUP BY). In the second query no. This changes the output format. Comparing the amount of records from one query to another is like comparing apples to oranges.

If you want to count the record amount of the first query programmatically, the ideal is to do this in the application layer. If you absolutely need to do this agnostically, the "dirty and fast" way is to encapsulate your query and do so:

SELECT COUNT(*) FROM (x) T

Where T is any arbitrary name and x is all the query you have already put in the question.