PostgreSQL COUNT function does not work

Navigation
2

The problem is simple but I can not understand where I am wrong, 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 this: 

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 she returns me 11 results in the count. I'm not good at sql my focus is front end so I'm kind of lost where I can go wrong in case anyone needs some info I did not put just ask in the comments.

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

    
asked by anonymous 16.06.2017 / 21:36

2 answers

3

Apparently, when you leave group by return 6 lines, with result 6 in each, correct? you can use the distinct for this.

ps. Try to indent the code to help with reading. 

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
    
16.06.2017 / 22:00
1

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

If you want to count the number of records of the first query programmatically, it is best to do this at the application layer. If you absolutely must do this agnostically, the "dirty and quick" way is to encapsulate your query and do so: 

SELECT COUNT(*) FROM (x) T

Where T is any arbitrary name and x is the entire query you've already placed in the question.

    
16.06.2017 / 21:42
Change one combobox from the other BOOTSTRAP Editions