Condição WHERE sem agrupamento de campo - POSTGRES
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>
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>
Sérgio Saibel
Curtidas 0
Melhor post
Emerson Nascimento
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_pessoaGOSTEI 1
Mais Respostas
Claudio Lopes
09/07/2019
coloque as tabelas com alguns inserts de exemplo (dados fictícios) para poder te ajudar melhor
https://rextester.com/l/postgresql_online_compiler
https://rextester.com/l/postgresql_online_compiler
GOSTEI 0
Sérgio Saibel
09/07/2019
coloque as tabelas com alguns inserts de exemplo (dados fictícios) para poder te ajudar melhor
https://rextester.com/l/postgresql_online_compiler
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>
GOSTEI 0
Sérgio Saibel
09/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_pessoaBoa 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"
:(
GOSTEI 0
Sérgio Saibel
09/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_pessoaBoa 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
GOSTEI 0
Emerson Nascimento
09/07/2019
aparentemente o problema era de onde estava sendo obtido o consumo.
agora está corrigido:
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_pessoaGOSTEI 1
Sérgio Saibel
09/07/2019
aparentemente o problema era de onde estava sendo obtido o consumo.
agora está corrigido:
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_pessoaSomente 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.
GOSTEI 0