GROUP BY with MAX()

Question

I am new to sql and I have a problem to resolve so i need some help please

For example, during the week 04/2019, 720 mutations are missing compared to the real information

In fact the problem comes from query . Indeed in this one a group by CODE_COMMUNE and NOM_SITE is made and the value MAX of the fields is returned for this commune and this site. This means that when there are several mutations on the same site of the same commune, only one mutation is returned, which explains the missing mutations.

So i would like to return all the mutation on the same site of the same commune.

How can i do that ?

`select IPO_VILLE.CODE_COMMUNE, 
   IPO_SITE.NOM_SITE,
     MAX(IPO_AT.DATE_MUTATION) DATE_MUTATION,
     MAX(IPO_AT.IAR_NDFICTIF) IAR_NDFICTIF,
     MAX(IPO_AT.MUTATION_REALISEE_PAR) MUTATION_REALISEE_PAR,
     MAX(IPO_AT.CAUSE_MUTATION) CAUSE_MUTATION,
     MAX(IPO_AT.CODE_BASICAT) CODE_BASICAT,
     MAX(IPO_AT.PIGTAIL_FIBRE_CONN_COURANT) FIBRE_COURANT,
     MAX(IPO_AT.PIGTAIL_FIBRE_CONN_CIBLE) FIBRE_CIBLE,
     MAX(IPO_AT.NOM_PB_COURANT) NOM_PB_COURANT,
     MAX(IPO_AT.NOM_PB) NOM_PB_CIBLE,
     MAX(IPO_AT.NOM_PM_COURANT) NOM_PM_COURANT,
     MAX(IPO_AT.NOM_PM) NOM_PM_CIBLE,
     MAX(IPO_AT.INTERVENTION_NUMBER) INTERVENTION_NUMBER,
     MAX(IPO_AT.GPC_BASE) GPC_BASE
from EDRPRE.IPO_AT IPO_AT
left join EDRPRE.IPO_LOGEMENT IPO_LOGEMENT ON IPO_AT.OBJECTID_LOGEMENT = IPO_LOGEMENT.OBJECTID_LOGEMENT
left join EDRPRE.IPO_SITE IPO_SITE ON IPO_LOGEMENT.OBJECTID_IMMEUBLE = IPO_SITE.OBJECTID_SITE
left join EDRPRE.IPO_RUE IPO_RUE ON IPO_SITE.OBJECTID_RUE = IPO_RUE.OBJECTID_RUE
left join EDRPRE.IPO_VILLE IPO_VILLE ON IPO_RUE.OBJECTID_COMMUNE  = IPO_VILLE.OBJECTID_COMMUNE
where IPO_AT.DATE_MUTATION is not null and IPO_AT.DATE_MUTATION >= TRUNC(sysdate-5, 'DD')
and IPO_VILLE.CODE_COMMUNE is not null and IPO_SITE.NOM_SITE is not null
group by IPO_VILLE.CODE_COMMUNE,IPO_SITE.NOM_SITE
order by IPO_VILLE.CODE_COMMUNE,IPO_SITE.NOM_SITE;`

user_1332870 · Answer

try this to get all records:

select IPO_VILLE.CODE_COMMUNE, 
   IPO_SITE.NOM_SITE,
     IPO_AT.DATE_MUTATION ,
     IPO_AT.IAR_NDFICTIF ,
     IPO_AT.MUTATION_REALISEE_PAR ,
     IPO_AT.CAUSE_MUTATION ,
     IPO_AT.CODE_BASICAT ,
     IPO_AT.PIGTAIL_FIBRE_CONN_COURANT ,
     IPO_AT.PIGTAIL_FIBRE_CONN_CIBLE ,
     IPO_AT.NOM_PB_COURANT ,
     IPO_AT.NOM_PB ,
     IPO_AT.NOM_PM_COURANT ,
     IPO_AT.NOM_PM ,
     IPO_AT.INTERVENTION_NUMBER ,
     IPO_AT.GPC_BASE 
from EDRPRE.IPO_AT IPO_AT
left join EDRPRE.IPO_LOGEMENT IPO_LOGEMENT ON IPO_AT.OBJECTID_LOGEMENT = IPO_LOGEMENT.OBJECTID_LOGEMENT
left join EDRPRE.IPO_SITE IPO_SITE ON IPO_LOGEMENT.OBJECTID_IMMEUBLE = IPO_SITE.OBJECTID_SITE
left join EDRPRE.IPO_RUE IPO_RUE ON IPO_SITE.OBJECTID_RUE = IPO_RUE.OBJECTID_RUE
left join EDRPRE.IPO_VILLE IPO_VILLE ON IPO_RUE.OBJECTID_COMMUNE  = IPO_VILLE.OBJECTID_COMMUNE
where IPO_AT.DATE_MUTATION is not null and IPO_AT.DATE_MUTATION >= TRUNC(sysdate-5, 'DD')
and IPO_VILLE.CODE_COMMUNE is not null and IPO_SITE.NOM_SITE is not null 
order by IPO_VILLE.CODE_COMMUNE,IPO_SITE.NOM_SITE;

GROUP BY with MAX()

1 Answers