Condição WHERE sem agrupamento de campo - POSTGRES

09/07/2019

17

Bom dia colegas ...

Necessito de sua ajuda, talvez alguém possa me ajudar ....

Possuo um SELECT onde devo agrupar por pessoas 'cod_pessoa', e em um SUBSELECT onde necessito utilizar um campo 'desagrupado' 'id_unidade' para fazer outra consulta, porém estes campos pertencem a mesma tabela, como posso fazer isso??

Segue um exemplo onde preciso agrupar por pessoa mas não por unidade, utilizo a unidade somente para a clausula WHERE (id_unidade) e buscar o consumo de gaz para aquela unidade, porém, se não agrupar o 'id_unidade' retorna erro e se agrupar calcula errado, pois existem mais de uma unidade por pessoa e nem todas unidades consomem gaz, sendo assim quando retorna 'null' não está agrupado por pessoa:

RETORNO:
cod_pessoa consumo_gaz
1 "30.9665306000"
2 "60.4063968000"
2 null
3 "76.4570036000"
3 null
4 "48.2962346000"

Banco POSTGRES

<SQL>
-- 17 -- Criação tabela unidades
CREATE TABLE unidades (
id_unidade serial not null,
cod_condominio int not null,
cod_pessoa int not null,
descricao varchar(255) not null,
area_privada decimal(11,5) not null,
area_comum decimal(11,5) not null,
area_total decimal(11,5) not null,
fracao_ideal decimal(11,5) not null,
num_matricula int not null,
observacoes text,

CONSTRAINT pk_unidades PRIMARY KEY (id_unidade)
);

-- 28 -- Criação de tabela de controle de gaz
CREATE TABLE controles_gaz(
id_controle_gaz serial not null,
cod_documento int not null,
cod_condominio int not null,
mes_ano varchar(45) not null,
consumo_kg decimal(11,5) not null,
vlr_unitario decimal(11,5) not null,
data_lancamento date not null default now(),
data_faturamento date,

CONSTRAINT pk_controles_gaz PRIMARY KEY(id_controle_gaz)
);

-- 29 -- Criação de tabela de controle de consumo de gaz por unidade
CREATE TABLE controles_gaz_unidades(
id_controle_gaz_unidade serial not null,
cod_unidade int not null,
cod_controle_gaz int not null,
leitura_atual int not null,
data_leitura date not null default now(),
consumo_kg decimal(11,5) not null,

CONSTRAINT pk_controles_gaz_unidades PRIMARY KEY(id_controle_gaz_unidade)
);


-- SELECT para cálculo das despesas de GAZ POR UNIDADES RESIDÊNCIAIS
SELECT cod_pessoa,
COALESCE(
(SELECT controles_gaz_unidades.consumo_kg * vlr_unitario
FROM controles_gaz_unidades
INNER JOIN controles_gaz ON id_controle_gaz = cod_controle_gaz
WHERE cod_unidade = id_unidade
AND EXTRACT('Month' FROM data_leitura) = EXTRACT('Month' FROM now())
AND EXTRACT('Year' FROM data_leitura) = EXTRACT('Year' FROM now())),null)
FROM unidades
WHERE id_unidade NOT IN (1,2,13)
GROUP BY cod_pessoa
ORDER BY cod_pessoa
</SQL>
Responder

Post mais votado

10/07/2019

acredito que seja algo assim:
SELECT
	uni.cod_pessoa,
	sum(coalesce(cg.consumo_kg,0)) totalconsumo,
	sum(coalesce(cg.consumo_kg * cg.vlr_unitario,0)) valorconsumo
FROM
	unidades uni
LEFT JOIN
	controles_gaz_unidades cgu on cgu.cod_unidade = uni.id_unidade
	AND extract('Month' FROM cgu.data_leitura) = extract('Month' FROM now())
	AND extract('Year' FROM cgu.data_leitura) = extract('Year' FROM now())
LEFT JOIN
	controles_gaz cg ON cg.id_controle_gaz = cgu.cod_controle_gaz
WHERE
	uni.id_unidade NOT IN (1,2,13)
GROUP BY uni.cod_pessoa
ORDER BY uni.cod_pessoa

Responder

Mais Posts

09/07/2019

Claudio Lopes

coloque as tabelas com alguns inserts de exemplo (dados fictícios) para poder te ajudar melhor
https://rextester.com/l/postgresql_online_compiler
Responder

10/07/2019

Sérgio Saibel

coloque as tabelas com alguns inserts de exemplo (dados fictícios) para poder te ajudar melhor
https://rextester.com/l/postgresql_online_compiler



Bom dia...
Obrigado pelo retorno...
Segue o solicitado.

<SQL>
INSERT INTO public.unidades (id_unidade, cod_condominio, cod_pessoa, descricao, area_privada, area_comum, area_total, fracao_ideal, num_matricula, observacoes) VALUES (32, 1, 9, 'Apartamento 301', 94.00000, 15.32270, 109.32270, 26.54880, 0, NULL);
INSERT INTO public.unidades (id_unidade, cod_condominio, cod_pessoa, descricao, area_privada, area_comum, area_total, fracao_ideal, num_matricula, observacoes) VALUES (16, 1, 9, 'Box 3', 11.37000, 24.15030, 35.52030, 4.74130, 0, NULL);
INSERT INTO public.unidades (id_unidade, cod_condominio, cod_pessoa, descricao, area_privada, area_comum, area_total, fracao_ideal, num_matricula, observacoes) VALUES (7, 1, 10, 'Box 15', 11.37000, 23.43030, 34.80030, 4.63440, 0, NULL);
INSERT INTO public.unidades (id_unidade, cod_condominio, cod_pessoa, descricao, area_privada, area_comum, area_total, fracao_ideal, num_matricula, observacoes) VALUES (24, 1, 1, 'Apartamento 101', 76.71000, 12.87170, 89.58170, 22.07380, 0, NULL);
INSERT INTO public.unidades (id_unidade, cod_condominio, cod_pessoa, descricao, area_privada, area_comum, area_total, fracao_ideal, num_matricula, observacoes) VALUES (25, 1, 2, 'Apartamento 102', 74.81000, 12.20370, 87.01370, 20.85440, 0, NULL);
INSERT INTO public.unidades (id_unidade, cod_condominio, cod_pessoa, descricao, area_privada, area_comum, area_total, fracao_ideal, num_matricula, observacoes) VALUES (20, 1, 2, 'Box 7', 11.83000, 24.18750, 36.01750, 4.80880, 0, NULL);
INSERT INTO public.unidades (id_unidade, cod_condominio, cod_pessoa, descricao, area_privada, area_comum, area_total, fracao_ideal, num_matricula, observacoes) VALUES (27, 1, 4, 'Apartamento 104', 94.00000, 15.32270, 109.32270, 26.54880, 0, NULL);
INSERT INTO public.unidades (id_unidade, cod_condominio, cod_pessoa, descricao, area_privada, area_comum, area_total, fracao_ideal, num_matricula, observacoes) VALUES (8, 1, 4, 'Box 16', 11.37000, 23.43030, 34.80030, 4.63440, 0, NULL);
INSERT INTO public.unidades (id_unidade, cod_condominio, cod_pessoa, descricao, area_privada, area_comum, area_total, fracao_ideal, num_matricula, observacoes) VALUES (15, 1, 5, 'Box 2', 11.37000, 24.15030, 35.52030, 4.74130, 0, NULL);
INSERT INTO public.unidades (id_unidade, cod_condominio, cod_pessoa, descricao, area_privada, area_comum, area_total, fracao_ideal, num_matricula, observacoes) VALUES (28, 1, 5, 'Apartamento 201', 94.00000, 15.32270, 109.32270, 26.54880, 0, NULL);
INSERT INTO public.unidades (id_unidade, cod_condominio, cod_pessoa, descricao, area_privada, area_comum, area_total, fracao_ideal, num_matricula, observacoes) VALUES (22, 1, 6, 'Box 9', 12.62000, 24.25140, 36.87140, 4.92560, 0, NULL);
INSERT INTO public.unidades (id_unidade, cod_condominio, cod_pessoa, descricao, area_privada, area_comum, area_total, fracao_ideal, num_matricula, observacoes) VALUES (29, 1, 6, 'Apartamento 202', 74.81000, 12.20370, 87.01370, 20.85440, 0, NULL);
INSERT INTO public.unidades (id_unidade, cod_condominio, cod_pessoa, descricao, area_privada, area_comum, area_total, fracao_ideal, num_matricula, observacoes) VALUES (30, 1, 7, 'Apartamento 203', 74.81000, 12.20370, 87.01370, 20.85440, 0, NULL);


INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (1, 24, 1, 575281, '2019-06-06', 5.53750);
INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (2, 25, 1, 705129, '2019-06-06', 7.98250);
INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (4, 27, 1, 380080, '2019-06-06', 10.10500);
INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (5, 28, 1, 1223638, '2019-06-06', 11.51250);
INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (6, 29, 1, 772509, '2019-06-06', 20.38500);
INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (7, 30, 1, 1498311, '2019-06-06', 24.80000);
INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (9, 32, 1, 830301, '2019-06-06', 1.50250);
INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (21, 24, 1, 576782, '2019-07-04', 3.75250);
INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (22, 25, 1, 708057, '2019-07-04', 7.32000);
INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (24, 27, 1, 382421, '2019-07-04', 5.85250);
INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (25, 28, 1, 1227902, '2019-07-04', 10.66000);
INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (26, 29, 1, 781349, '2019-07-04', 22.10000);
INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (27, 30, 1, 1509286, '2019-07-04', 27.43750);
INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (28, 31, 1, 782127, '2019-07-04', 8.37000);
INSERT INTO public.controles_gaz_unidades (id_controle_gaz_unidade, cod_unidade, cod_controle_gaz, leitura_atual, data_leitura, consumo_kg) VALUES (29, 32, 1, 830869, '2019-07-04', 1.42000);


INSERT INTO public.controles_gaz (id_controle_gaz, cod_documento, mes_ano, consumo_kg, vlr_unitario, data_lancamento, data_faturamento) VALUES (1, 1, '2019/06', 524.00000, 8.25224, '2019-05-22', '2019-06-05');
</SQL>
Responder

10/07/2019

Sérgio Saibel

acredito que seja algo assim:
SELECT
	uni.cod_pessoa,
	sum(coalesce(cg.consumo_kg,0)) totalconsumo,
	sum(coalesce(cg.consumo_kg * cg.vlr_unitario,0)) valorconsumo
FROM
	unidades uni
LEFT JOIN
	controles_gaz_unidades cgu on cgu.cod_unidade = uni.id_unidade
	AND extract('Month' FROM cgu.data_leitura) = extract('Month' FROM now())
	AND extract('Year' FROM cgu.data_leitura) = extract('Year' FROM now())
LEFT JOIN
	controles_gaz cg ON cg.id_controle_gaz = cgu.cod_controle_gaz
WHERE
	uni.id_unidade NOT IN (1,2,13)
GROUP BY uni.cod_pessoa
ORDER BY uni.cod_pessoa



Boa tarde...

Obrigado pela tentativa, mas nada feito...
Eis o resultado do SQL

1 "524.00000" "4324.1737600000"
2 "524.00000" "4324.1737600000"
3 "524.00000" "4324.1737600000"
4 "524.00000" "4324.1737600000"
5 "524.00000" "4324.1737600000"
6 "524.00000" "4324.1737600000"
7 "524.00000" "4324.1737600000"
8 "524.00000" "4324.1737600000"

:(
Responder

10/07/2019

Sérgio Saibel

acredito que seja algo assim:
SELECT
	uni.cod_pessoa,
	sum(coalesce(cg.consumo_kg,0)) totalconsumo,
	sum(coalesce(cg.consumo_kg * cg.vlr_unitario,0)) valorconsumo
FROM
	unidades uni
LEFT JOIN
	controles_gaz_unidades cgu on cgu.cod_unidade = uni.id_unidade
	AND extract('Month' FROM cgu.data_leitura) = extract('Month' FROM now())
	AND extract('Year' FROM cgu.data_leitura) = extract('Year' FROM now())
LEFT JOIN
	controles_gaz cg ON cg.id_controle_gaz = cgu.cod_controle_gaz
WHERE
	uni.id_unidade NOT IN (1,2,13)
GROUP BY uni.cod_pessoa
ORDER BY uni.cod_pessoa



Boa tarde...

Obrigado pela tentativa, mas nada feito...
Eis o resultado do SQL

1 "524.00000" "4324.1737600000"
2 "524.00000" "4324.1737600000"
3 "524.00000" "4324.1737600000"
4 "524.00000" "4324.1737600000"
5 "524.00000" "4324.1737600000"
6 "524.00000" "4324.1737600000"
7 "524.00000" "4324.1737600000"
8 "524.00000" "4324.1737600000"

:(


O que eu estaria necessitando seria algo do tipo:

1 "165.0705094998579731" "58.4165306000000000"
2 "160.4230892658923418" "87.8563968000000000"
2 "68.1330691197248488"
3 "160.4230892658923418" "103.9070036000000000"
3 "66.3801519707298682"
4 "200.7966476769278816" "75.7462346000000000"
4 "65.9302498406037561"
5 "67.2332648594726246"
5 "200.7966476769278816" "115.4188784000000000"

só que sem duplicar a primeira coluna
Responder
aparentemente o problema era de onde estava sendo obtido o consumo.
agora está corrigido:
SELECT
    uni.cod_pessoa,
    sum(coalesce(cgu.consumo_kg,0)) totalconsumo,
    sum(coalesce(cgu.consumo_kg * cg.vlr_unitario,0)) valorconsumo
FROM
    unidades uni
LEFT JOIN
    controles_gaz_unidades cgu on cgu.cod_unidade = uni.id_unidade
    AND Month(cgu.data_leitura) = Month(getdate())
    AND Year(cgu.data_leitura) = Year(getdate())
LEFT JOIN
    controles_gaz cg ON cg.id_controle_gaz = cgu.cod_controle_gaz
WHERE
    uni.id_unidade NOT IN (1,2,13)
GROUP BY uni.cod_pessoa
ORDER BY uni.cod_pessoa
Responder

11/07/2019

Sérgio Saibel

aparentemente o problema era de onde estava sendo obtido o consumo.
agora está corrigido:
SELECT
    uni.cod_pessoa,
    sum(coalesce(cgu.consumo_kg,0)) totalconsumo,
    sum(coalesce(cgu.consumo_kg * cg.vlr_unitario,0)) valorconsumo
FROM
    unidades uni
LEFT JOIN
    controles_gaz_unidades cgu on cgu.cod_unidade = uni.id_unidade
    AND Month(cgu.data_leitura) = Month(getdate())
    AND Year(cgu.data_leitura) = Year(getdate())
LEFT JOIN
    controles_gaz cg ON cg.id_controle_gaz = cgu.cod_controle_gaz
WHERE
    uni.id_unidade NOT IN (1,2,13)
GROUP BY uni.cod_pessoa
ORDER BY uni.cod_pessoa


Somente troquei ....
[code/sql]
AND Month(cgu.data_leitura) = Month(getdate())
AND Year(cgu.data_leitura) = Year(getdate())
[/code]
por ...
[code/sql]
AND extract('Month' FROM cgu.data_leitura) = extract('Month' FROM now())
AND extract('Year' FROM cgu.data_leitura) = extract('Year' FROM now())
[/code]

Pois o postgres retornava erros.
Tudo certo muito obrigado...

Ps. Existe uma forma de marcar como resolvido?? Obrigado novamente.
Responder

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários. Para saber mais sobre o uso de cookies,
consulte nossa política de privacidade. Ao continuar navegando em nosso site, você concorda com a nossa política.

Aceitar