Ir para conteúdo

POWERED BY:

Arquivado

Este tópico foi arquivado e está fechado para novas respostas.

FabioMSouza

Ajuda com SQL - count case when having

Por , em MySQL

Recommended Posts

FabioMSouza    0

FabioMSouza
Postado

Bom dia, não consigo obter o total de registros para uma consulta ao usar having para filtrar resultados. O sql para contar os resultados funciona quando não uso having e group by, para filtrar os registros. Entendo o básico de SQL.

 

Alguém pode me ajudar, quero entender porque não funciona, onde estou errando?

 

Lista imoveis
----------------------------------------------------------------------------
SELECT dc.*, dcc.*, a.*,
// custom fields
MAX(CASE WHEN cf.dynamic_custom_key = 'custom_dynamic_sku' THEN cf.dynamic_custom_value ELSE NULL END) as custom_dynamic_sku,
MAX(CASE WHEN cf.dynamic_custom_key = 'custom_dynamic_instock' THEN cf.dynamic_custom_value ELSE NULL END) as custom_dynamic_instock,
MAX(CASE WHEN cf.dynamic_custom_key = 'custom_dynamic_valor_venda' THEN cf.dynamic_custom_value ELSE NULL END) as custom_dynamic_valor_venda,
MAX(CASE WHEN cf.dynamic_custom_key = 'custom_dynamic_valor_aluguel' THEN cf.dynamic_custom_value ELSE NULL END) as custom_dynamic_valor_aluguel,
MAX(CASE WHEN cf.dynamic_custom_key = 'custom_dynamic_valor_diaria' THEN cf.dynamic_custom_value ELSE NULL END) as custom_dynamic_valor_diaria,
MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_dormitorios' THEN cf.dynamic_custom_value ELSE NULL END) as custom_imoveis_dormitorios,
MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_suites' THEN cf.dynamic_custom_value ELSE NULL END) as custom_imoveis_suites,
MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_banheiros' THEN cf.dynamic_custom_value ELSE NULL END) as custom_imoveis_banheiros,
MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_vagas' THEN cf.dynamic_custom_value ELSE NULL END) as custom_imoveis_vagas,
MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_tipo' THEN cf.dynamic_custom_value ELSE NULL END) as custom_imoveis_tipo,
MAX(CASE WHEN cf.dynamic_custom_key = 'custom_dynamic_area_total' THEN cf.dynamic_custom_value ELSE NULL END) as custom_dynamic_area_total,
MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_transacao' THEN cf.dynamic_custom_value ELSE NULL END) as custom_imoveis_transacao
FROM dynamic_contents AS dc
LEFT JOIN dynamic_contents_content AS dcc ON dc.dynamic_id=dcc.dynamic_content_parent_id
LEFT JOIN archives As a ON dc.dynamic_archives_id=a.archives_id
LEFT JOIN dynamic_contents_custom as cf ON dc.dynamic_id = cf.dynamic_custom_parent_id
WHERE dynamic_type='imoveis' AND dynamic_published='1' AND dynamic_modered='0' AND dynamic_deleted='0'
// group by
GROUP BY dc.dynamic_id
// having
HAVING custom_imoveis_transacao='venda'
//orderby
ORDER BY ID DESC
//limit
LIMIT 0,10
contar registros para páginação com filtro transação retorna valor errado
------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT dc.dynamic_id) as total
FROM dynamic_contents AS dc
LEFT JOIN dynamic_contents_content AS dcc ON dc.dynamic_id=dcc.dynamic_content_parent_id
LEFT JOIN archives As a ON dc.dynamic_archives_id=a.archives_id
LEFT JOIN dynamic_contents_custom as cf ON dc.dynamic_id = cf.dynamic_custom_parent_id
WHERE dynamic_type='$type' AND dynamic_published='1' AND dynamic_modered='0' AND dynamic_deleted='0'
// group by
GROUP BY dc.dynamic_id
// having
HAVING ( MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_transacao' THEN cf.dynamic_custom_value ELSE NULL END)='venda'
//orderby
ORDER BY ID DESC
contar registros para páginação sem filtro transação (having), esta retorna os valores corretos
------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT dc.dynamic_id) as total
FROM dynamic_contents AS dc
LEFT JOIN dynamic_contents_content AS dcc ON dc.dynamic_id=dcc.dynamic_content_parent_id
LEFT JOIN archives As a ON dc.dynamic_archives_id=a.archives_id
LEFT JOIN dynamic_contents_custom as cf ON dc.dynamic_id = cf.dynamic_custom_parent_id
WHERE dynamic_type='$type' AND dynamic_published='1' AND dynamic_modered='0' AND dynamic_deleted='0'
//orderby
ORDER BY ID DESC

Compartilhar este post

Link para o post
Compartilhar em outros sites

Motta    645

Motta
Postado

O having é usado para condicoes do agrupamento , diferente do where , por exemplo

 

--vendas acima de 100 reais

 

Select *from vendas where valor > 100

 

 

--setor com vendas totais acima de 10000 reais

 

Select codsetor,sum(valor) as valor from vendas group by codsetor having sum(valor) > 10000

Compartilhar este post

Link para o post
Compartilhar em outros sites

FabioMSouza    0

FabioMSouza
Postado

Motta,

 

Estou usando having para filtrar o agrupamento que fiz com MAX (CASE), onde pego linhas da tabela custom com dados do imóvel, no teste tenho 09 itens, 3 locação e 6 venda, quando listo o geral consigo pegar o total para fazer a páginação, quando uso filtro com having, retorna um total de 31, que é o total de linhas de atributos na tabela custom. Não consigo pegar o total de 03 ou 06 com os filtros.

 

Mas o retorno da lista de imóveis vem correta, o erro ocorre só na consulta com count. Existe outra forma de calcular o total de registros da query?

 

obrigado :)

Compartilhar este post

Link para o post
Compartilhar em outros sites

FabioMSouza    0

FabioMSouza
Postado

Segue o resultados do SQL, assim deve ficar mais claro.

Todo tipo de imóvel (venda / locação), possui 09 itens. Retorna a lista de imóveis sem problemas.

 

Conta total de registros para paginação:

 

SELECT COUNT(DISTINCT dc.dynamic_id) as total
FROM dynamic_contents AS dc LEFT JOIN dynamic_contents_content AS dcc ON dc.dynamic_id=dcc.dynamic_content_parent_id LEFT JOIN archives As a ON dc.dynamic_archives_id=a.archives_id LEFT JOIN dynamic_contents_custom as cf ON dc.dynamic_id = cf.dynamic_custom_parent_id
WHERE dynamic_type='imoveis' AND dynamic_published='1' AND dynamic_modered='0' AND dynamic_deleted='0' ORDER BY dynamic_id DESC]

 

Retorna:

 

(row) total
1 9

 

 

Usando o filtro com HAVING, só imóvel venda, possui 06 itens. Retorna a lista de imóveis sem problemas.

Conta total de registros para paginação ( aqui não retornou como esperado):

 

SELECT COUNT(DISTINCT dc.dynamic_id) as total

 

FROM dynamic_contents AS dc LEFT JOIN dynamic_contents_content AS dcc ON dc.dynamic_id=dcc.dynamic_content_parent_id LEFT JOIN archives As a ON dc.dynamic_archives_id=a.archives_id LEFT JOIN dynamic_contents_custom as cf ON dc.dynamic_id = cf.dynamic_custom_parent_id

 

WHERE dynamic_type='imoveis' AND dynamic_published='1' AND dynamic_modered='0' AND dynamic_deleted='0'

 

GROUP BY dc.dynamic_id

 

HAVING ( MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_transacao' THEN cf.dynamic_custom_value ELSE NULL END)='venda' )

 

ORDER BY dynamic_id DESC

 

Retorna:

 

(row) total

1 1
2 1
3 1
4 1
5 1
6 1
Como obter o total = 6?

Compartilhar este post

Link para o post
Compartilhar em outros sites

Motta    645

Motta
Postado

Tentou tirar o grrup by da 2@ sql ?

 

O having tambem pode ser apenas uma condicao where, creio.

Compartilhar este post

Link para o post
Compartilhar em outros sites

FabioMSouza    0

FabioMSouza
Postado

Tentou tirar o grrup by da 2@ sql ?

 

O having tambem pode ser apenas uma condicao where, creio.

 

Sou obrigado a usar o HAVING devido a agregação, pois estou fazendo um Pivot com a tabela dynamic_contents_custom, e o GROUP BY é exigido pelo HAVING.

 

Tentei usar apenas Where, mas ele não filtra dados da agregação.

 

debug sql somente where:

 

Last Error -- [HY000, 1, misuse of aggregate: MAX()]

Query [19] -- [SELECT dc.*, dcc.*, a.* , MAX(CASE WHEN cf.dynamic_custom_key = 'custom_dynamic_sku' THEN cf.dynamic_custom_value ELSE NULL END) as custom_dynamic_sku, MAX(CASE WHEN cf.dynamic_custom_key = 'custom_dynamic_instock' THEN cf.dynamic_custom_value ELSE NULL END) as custom_dynamic_instock, MAX(CASE WHEN cf.dynamic_custom_key = 'custom_dynamic_valor_venda' THEN cf.dynamic_custom_value ELSE NULL END) as custom_dynamic_valor_venda, MAX(CASE WHEN cf.dynamic_custom_key = 'custom_dynamic_valor_aluguel' THEN cf.dynamic_custom_value ELSE NULL END) as custom_dynamic_valor_aluguel, MAX(CASE WHEN cf.dynamic_custom_key = 'custom_dynamic_valor_diaria' THEN cf.dynamic_custom_value ELSE NULL END) as custom_dynamic_valor_diaria, MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_dormitorios' THEN cf.dynamic_custom_value ELSE NULL END) as custom_imoveis_dormitorios, MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_suites' THEN cf.dynamic_custom_value ELSE NULL END) as custom_imoveis_suites, MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_banheiros' THEN cf.dynamic_custom_value ELSE NULL END) as custom_imoveis_banheiros, MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_vagas' THEN cf.dynamic_custom_value ELSE NULL END) as custom_imoveis_vagas, MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_tipo' THEN cf.dynamic_custom_value ELSE NULL END) as custom_imoveis_tipo, MAX(CASE WHEN cf.dynamic_custom_key = 'custom_dynamic_area_total' THEN cf.dynamic_custom_value ELSE NULL END) as custom_dynamic_area_total, MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_transacao' THEN cf.dynamic_custom_value ELSE NULL END) as custom_imoveis_transacao FROM dynamic_contents AS dc LEFT JOIN dynamic_contents_content AS dcc ON dc.dynamic_id=dcc.dynamic_content_parent_id LEFT JOIN archives As a ON dc.dynamic_archives_id=a.archives_id LEFT JOIN dynamic_contents_custom as cf ON dc.dynamic_id = cf.dynamic_custom_parent_id WHERE dynamic_type='imoveis' AND dynamic_published='1' AND dynamic_modered='0' AND dynamic_deleted='0' AND custom_imoveis_transacao='venda' GROUP BY dc.dynamic_id ORDER BY dynamic_id DESC LIMIT 0,3]

Query Result..

No Results

Compartilhar este post

Link para o post
Compartilhar em outros sites

FabioMSouza    0

FabioMSouza
Postado

Tentou tirar o grrup by da 2@ sql ?

 

O having tambem pode ser apenas uma condicao where, creio.

 

Sem GROUP BY:

 

Last Error -- [HY000, 1, a GROUP BY clause is required before HAVING]

Query [20] -- [SELECT COUNT(DISTINCT dc.dynamic_id) as total FROM dynamic_contents AS dc LEFT JOIN dynamic_contents_content AS dcc ON dc.dynamic_id=dcc.dynamic_content_parent_id LEFT JOIN archives As a ON dc.dynamic_archives_id=a.archives_id LEFT JOIN dynamic_contents_custom as cf ON dc.dynamic_id = cf.dynamic_custom_parent_id WHERE dynamic_type='imoveis' AND dynamic_published='1' AND dynamic_modered='0' AND dynamic_deleted='0' HAVING ( MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_transacao' THEN cf.dynamic_custom_value ELSE NULL END)='venda' ) ORDER BY dynamic_id DESC]

Query Result..

No Results

Compartilhar este post

Link para o post
Compartilhar em outros sites

Motta    645

Motta
Postado

Fica difícil analisar queries grandes não identadas e sem conhecer o modelo.

 

 

A ideia do

 

HAVING ( MAX(CASE WHEN cf.dynamic_custom_key = 'custom_imoveis_transacao' THEN cf.dynamic_custom_value ELSE NULL END)='venda' )

 

 

 

e pegar somente os casos em que

 

cf.dynamic_custom_value ='venda'

 

?

 

 

Não seria melhor filtar isto no where

 

Uso sempre a seguinte estratégia para consultas deste tipo

 

Começo sempre com uma sql que liste analiticamente TODOS os dados que precise

 

Vou montando a sql para totalizar em partes (se possível)

 

Mantenho sempre a identação , pois senão eu ao menos me perco em sql grandes

 

Testo para um universo pequeno (vendedor , filial , imovel etc)

Compartilhar este post

Link para o post
Compartilhar em outros sites

FabioMSouza    0

FabioMSouza
Postado

Bom dia,

 

Segue um exemplo detalhado do problema, não consigo obter o total de registros para fazer a paginação, os SQL usados no sistema estão no inicio do post.

Quero entender porque o count com filtro 'venda' feito com having me retorna 3 linhas em vez de pegar o total.

 

Desde já agradeço a ajuda! :D

 

imovel
-------------------------------------------
id_imovel imovel publicado
----------- ---------- --------------
1 imovel 01 1
2 imovel 02 1
3 imovel 03 1
4 imovel 04 1
imovel_custom
------------------------------------------
imovel_id key value
----------- ---------- ----------
1 transacao locacao
1 valor 10.0
1 area 120.0
2 transacao venda
2 valor 100.0
2 area 130.0
3 transacao venda
3 valor 110.0
3 area 140.0
4 transacao venda
4 valor 120.0
4 area 150.0
Lista com todos os tipos de transação:
SELECT
i.*, ic.*,
MAX(CASE WHEN ic.key = 'transacao' THEN ic.value ELSE NULL END) AS transacao,
MAX(CASE WHEN ic.key = 'valor' THEN ic.value ELSE NULL END) AS valor,
MAX(CASE WHEN ic.key = 'area' THEN ic.value ELSE NULL END) AS area
FROM
imovel AS i
LEFT JOIN
imovel_custom AS ic ON i.id_imovel = ic.imovel_id
WHERE
publicado='1'
GROUP BY
i.id_imovel
Resultado
--------------------------------------------------------------------------------------
id_imovel imovel publicado transacao valor area
----------- ---------- ------------ ---------- ---------- ----------
1 imovel 01 1 locacao 10.0 120.0
2 imovel 02 1 venda 100.0 130.0
3 imovel 03 1 venda 110.0 140.0
4 imovel 04 1 venda 120.0 150.0
Count para paginação com todos os tipos de transação:
SELECT
COUNT(DISTINCT i.id_imovel) as total
FROM
imovel AS i
LEFT JOIN
imovel_custom AS ic ON i.id_imovel = ic.imovel_id
WHERE
publicado='1'
Resultado
---------------------------------------------------------------
total
---------
4
Lista com transação venda:
SELECT
i.*, ic.*,
MAX(CASE WHEN ic.key = 'transacao' THEN ic.value ELSE NULL END) AS transacao,
MAX(CASE WHEN ic.key = 'valor' THEN ic.value ELSE NULL END) AS valor,
MAX(CASE WHEN ic.key = 'area' THEN ic.value ELSE NULL END) AS area
FROM
imovel AS i
LEFT JOIN
imovel_custom AS ic ON i.id_imovel = ic.imovel_id
WHERE
publicado='1'
GROUP BY
i.id_imovel
HAVING
transacao='venda'
Resultado
--------------------------------------------------------------------------------------
id_imovel imovel publicado transacao valor area
----------- ---------- ------------ ---------- ---------- ----------
2 imovel 02 1 venda 100.0 130.0
3 imovel 03 1 venda 110.0 140.0
4 imovel 04 1 venda 120.0 150.0
Count para paginação com transação venda:
SELECT
COUNT(DISTINCT i.id_imovel) as total
FROM
imovel AS i
LEFT JOIN
imovel_custom AS ic ON i.id_imovel = ic.imovel_id
WHERE
publicado='1'
GROUP BY
i.id_imovel
HAVING
( MAX(CASE WHEN ic.key = 'transacao' THEN ic.value ELSE NULL END)='venda' )
Resultado
---------------------------------------------------------------
total
---------
1
1
1

Compartilhar este post

Link para o post
Compartilhar em outros sites

Motta    645

Motta
Postado

SELECT COUNT(DISTINCT i.id_imovel) as total

FROM imovel AS i

LEFT JOIN imovel_custom AS ic ON i.id_imovel = ic.imovel_id

WHERE publicado='1'

And transacao = 'venda'

Compartilhar este post

Link para o post
Compartilhar em outros sites

FabioMSouza    0

FabioMSouza
Postado

SELECT COUNT(DISTINCT i.id_imovel) as total

FROM imovel AS i

LEFT JOIN imovel_custom AS ic ON i.id_imovel = ic.imovel_id

WHERE publicado='1'

And transacao = 'venda'

 

Com esta consulta retorna...

 

Mensagens do MySQL : dot.gif

#1054 - Unknown column 'transacao' in 'where clause'

 

transação é um apelido do agrupamento, testei pelo 'value'...

 

SELECT

COUNT(DISTINCT i.id_imovel) as total

FROM
imovel AS i
LEFT JOIN
imovel_custom AS ic ON i.id_imovel = ic.imovel_id
WHERE
publicado='1' And value = 'venda'
Nessa linha de pensamento estou testando com múltiplos filtros...
SELECT
COUNT(DISTINCT i.id_imovel) as total
FROM
imovel AS i
LEFT JOIN
imovel_custom AS ic ON i.id_imovel = ic.imovel_id
WHERE
publicado='1'
AND (ic.key='transacao' AND ic.value ='venda')
AND (ic.key='valor' AND ic.value >= 110)
AND (ic.key='area' AND ic.value >= 120)
Não da erro, mas dependendo a query retorna total errado, provavelmente devido a falta de tipagem...
pesquisando como resolver...
Grato pela ajuda! :D

Compartilhar este post

Link para o post
Compartilhar em outros sites

Motta    645

Motta
Postado

apelando ... rsrs

select COUNT(DISTINCT i.id_imovel) as total
 from
(SELECT
        i.*, ic.*, 
        MAX(CASE WHEN ic.key = 'transacao' THEN ic.value ELSE NULL END) AS transacao,
        MAX(CASE WHEN ic.key = 'valor' THEN ic.value ELSE NULL END) AS valor,        
        MAX(CASE WHEN ic.key = 'area' THEN ic.value ELSE NULL END) AS area
FROM
        imovel AS i
LEFT JOIN 
        imovel_custom AS ic ON i.id_imovel = ic.imovel_id
WHERE 
        publicado='1'
GROUP BY
        i.id_imovel


HAVING 
        transacao='venda'

) virtual

Compartilhar este post

Link para o post
Compartilhar em outros sites
Ir para a lista de tópicos MySQL
×

Informação importante

Ao usar o fórum, você concorda com nossos Termos e condições.

  Aceito