sql query returns incorrect result

Question

sql query returns incorrect result

Navigation

#1 by (1 votes)

1

Hi guys I have the following query, I'm working in PostgreSQL, and my base is a survey applied to people in certain areas, basically you need to make an analysis that if you have some kind of animal this family, put the number one (as if it were a check box)

select distinct no, FOLIO_FAMILIA, ID_MUNINICPIO, ID_LOCALIDAD,
                PERROS, CERDOS, VACAS, LOROS
                from (
                select distinct row_number() OVER (order by e.FOLIO_FAMILIA, 
                e.ID_MUNICIPIO, e.ID_LOCALIDAD) as No, 
                e.FOLIO_FAMILIA,  e.ID_MUNICIPIO, e.ID_LOCALIDAD,
                count( case when id_caso=1 then 1 else null end) as PERROS,
                count( case when id_caso=2 then 1 else null end) as CERDOS,
                count( case when id_caso=3 then 1 else null end) as VACAS,
                count( case when id_caso=4 then 1 else null end) as LOROS
                from encuesta e , bienes b, zona z
                where e.FOLIO_FAMILIA = b.FOLIO_FAMILIA and
                e.FOLIO_FAMILIA = z.FOLIO    
                and e.ID_MUNICIPIO = '12-15'
                and id_caso in (1,2,3,4) and z.id_zona='12-15-1'        
                group by e.FOLIO_FAMILIA, e.ID_MUNICIPIO, e.ID_LOCALIDAD
                order by e.FOLIO_FAMILIA, e.ID_MUNICIPIO, e.ID_LOCALIDAD) as t1;

the result looks like this

Instead of putting the one as I indicated in the count case, it puts 2

I hope you can help me I appreciate it

sql postgresql

asked by IndiraRivas 07.03.2018 в 16:59

source

1 answer

How to compress an XML file in ZIP in VBA Excel? Compare values between two HashMap

score 1 · Accepted Answer

Actually your query is solved easier than you think ...

Your inquiry is fine. The problem you are having is using a count. The funcino that counts all the occurrences that come from rows for that row.

A practical solution to what you need is to use a MAX instead of a count. how are you returning 0 or 1, if there is any 1, it will return 1, and if not, it will return 0.

select distinct no, FOLIO_FAMILIA, ID_MUNINICPIO, ID_LOCALIDAD,
            PERROS, CERDOS, VACAS, LOROS
            from (
            select distinct row_number() OVER (order by e.FOLIO_FAMILIA, 
            e.ID_MUNICIPIO, e.ID_LOCALIDAD) as No, 
            e.FOLIO_FAMILIA,  e.ID_MUNICIPIO, e.ID_LOCALIDAD,
            MAX( case when id_caso=1 then 1 else 0 end) as PERROS,
            MAX( case when id_caso=2 then 1 else 0 end) as CERDOS,
            MAX( case when id_caso=3 then 1 else 0 end) as VACAS,
            MAX( case when id_caso=4 then 1 else 0 end) as LOROS
            from encuesta e , bienes b, zona z
            where e.FOLIO_FAMILIA = b.FOLIO_FAMILIA and
            e.FOLIO_FAMILIA = z.FOLIO    
            and e.ID_MUNICIPIO = '12-15'
            and id_caso in (1,2,3,4) and z.id_zona='12-15-1'        
            group by e.FOLIO_FAMILIA, e.ID_MUNICIPIO, e.ID_LOCALIDAD
            order by e.FOLIO_FAMILIA, e.ID_MUNICIPIO, e.ID_LOCALIDAD) as t1;