0

I was trying to do the UNION with two Queries and i received 42601 error. Can you help me to undersant how to fixed it? enter image description here

CODE :

    SELECT Date_part('year', pr.data_chiusura)  AS anno,
       Date_part('month', pr.data_chiusura) AS mese,
       im.comune                            AS comune,
       provincia.descrizione                AS provincia,
       regione.descrizione                  AS regione,
       Count(pr.id_pratica)                 AS numero_domande,
       Sum(di.costo)                        AS costo_lavori,
       im.zona_sismica 
    FROM   pncs_pratica pr,
       pncs_immobile im,
       pncs_dichiarazione di,
       pncs_comune comune,
       pncs_provincia provincia,
       pncs_regione regione
    UNION ALL
    SELECT    Date_part('year', pr.data_chiusura)  AS anno,
          Date_part('month', pr.data_chiusura) AS mese,
          im.comune                            AS comune,
          provincia.descrizione                AS provincia,
          regione.descrizione                  AS regione,
          Count(dati_ante.id_dato_intervento)  AS numero_domande_ante,
          dati_ante.classe_rischio             AS cl_risc_ante,
          Avg(dati_ante.indice_pam)            AS avg_pam_ante,
          Avg(dati_ante.indice_isv)            AS avg_isv_ante,
          Count(dati_post.id_dato_intervento)  AS numero_domande_post,
          dati_post.classe_rischio             AS cl_risc_post,
          Avg(dati_post.indice_pam)            AS avg_pam_post,
          Avg(dati_post.indice_isv)            AS avg_isv_post
    FROM      pncs_pratica pr
    LEFT JOIN
          (
                 SELECT *
                 FROM   pncs_dati_intervento pdi
                 WHERE  pdi.flag_ante = true) AS dati_ante
ON pr.id_pratica = dati_post.id_pratica,
pncs_immobile im,
pncs_dichiarazione di,
pncs_comune comune,
pncs_provincia provincia,
pncs_regione regione
--pncs_stato_pratica stpratica
WHERE pr.id_pratica = im.id_pratica
AND pr.id_pratica = di.id_pratica
AND im.cod_comune = comune.codice_istat
AND im.cod_provincia = provincia.codice
AND im.cod_regione = regione.codice
AND pr.data_chiusura IS NOT NULL
AND pr.id_stato_pratica = 3
AND pr.flag_rimosso IS false
--AND stpratica.descrizione = 'FIRMATA'
--and pr.id_stato_pratica = stpratica.id_stato_pratica

GROUP BY Date_part('year', pr.data_chiusura),
Date_part('month', pr.data_chiusura),
regione.descrizione,
provincia.descrizione,
im.comune,
im.zona_sismica
dati_ante.classe_rischio,
dati_post.classe_rischio

ORDER BY anno DESC,
mese DESC,
comune ASC
    

1
  • 1
    seems union all two query have different amount of columns. first select query is 8 columns, second query have 13 columns. ?
    – jian
    Dec 7, 2022 at 5:46

2 Answers 2

1

You're missing a comma in the GROUP BY clause after the im.zona_sismica column. Your GROUP BY clause should be:

GROUP BY Date_part('year', pr.data_chiusura), Date_part('month', pr.data_chiusura), regione.descrizione, provincia.descrizione, im.comune, im.zona_sismica, dati_ante.classe_rischio, dati_post.classe_rischio
4
  • Thanks, i'm just fixed and an error appear that says : "column pr.data_chiusura" must appear in the GROUP BY clause or be used in aggregate function." Dec 6, 2021 at 13:49
  • But pr.data_chiusura already existi in the group by Dec 6, 2021 at 13:50
  • i fixed the problem about group by, but told to me that UNION should be have same column. Is possible to have an operator that unify this two queries? Dec 6, 2021 at 14:14
  • 1
    @umbertomauro It is possible to unify the two queries if you SELECT the same number and type of columns in both queries. You currently are not doing that. How do you expect the final results to look like for the columns that you're not selecting in your first query, such as avg_pam_ante?
    – J.D.
    Dec 6, 2021 at 14:20
-1

To my understanding, while performing union operations we need the same number of columns on both parts of the UNION.

Your Query:
Table 1 output columns:

SELECT 
       Date_part('year', pr.data_chiusura)  AS anno,
       Date_part('month', pr.data_chiusura) AS mese,
       im.comune                            AS comune,
       provincia.descrizione                AS provincia,
       regione.descrizione                  AS regione,
       Count(pr.id_pratica)                 AS numero_domande,
       Sum(di.costo)                        AS costo_lavori,
       im.zona_sismica 

Number of output columns: 8

Table 2 output columns:

SELECT
      Date_part('year', pr.data_chiusura)  AS anno,
      Date_part('month', pr.data_chiusura) AS mese,
      im.comune                            AS comune,
      provincia.descrizione                AS provincia,
      regione.descrizione                  AS regione,
      Count(dati_ante.id_dato_intervento)  AS numero_domande_ante,
      dati_ante.classe_rischio             AS cl_risc_ante,
      Avg(dati_ante.indice_pam)            AS avg_pam_ante,
      Avg(dati_ante.indice_isv)            AS avg_isv_ante,
      Count(dati_post.id_dato_intervento)  AS numero_domande_post,
      dati_post.classe_rischio             AS cl_risc_post,
      Avg(dati_post.indice_pam)            AS avg_pam_post,
      Avg(dati_post.indice_isv)            AS avg_isv_post

Number of output columns: 13

4
  • Only output column names in the first SELECT are relevant. The rest is noise. Dec 7, 2021 at 1:15
  • @ErwinBrandstetter they edited/corrected the answer (actually a year ago, just noticed! ;) Dec 7, 2022 at 8:20
  • @ypercubeᵀᴹ: The leading statement is still incorrect like I commented. Dec 7, 2022 at 8:41
  • ah, sigh, didn't notice that ... Dec 7, 2022 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.