Postgresql query GROUP BY not possible

Question

In my project I have this complex query:

SELECT FT."Riferimento1", FT."Riferimento2", "CodProdotto", "QuantitaFatturata", "PrezzoUnit", "DataFattura", "NumeroFattura", "CodCli"
FROM public.idocuments_as_fatturetestata FT
LEFT JOIN public.idocuments_as_fatturerighe FR ON FT."Riferimento1" = FR."Riferimento1" AND FT."Riferimento2" = FR."Riferimento2"
WHERE FT."CodCli" = '12192' GROUP BY "NumeroFattura";

If I don't use the GROUP BY option all was done but I have to grouping by NumeroFattura column.

When I add the Group BY sentence I get this error:

ERROR: column "ft.Riferimento1" must appear in the GROUP BY clause or be used in an aggregate function

if I add ft.Riferimento1 system ask me ft.Riferimento2 also and all columns, but I want to group just for NumeroFattura column.

"25"    "000006"    "191215002N"    1   1.800000    "2017-01-31 00:00:00+00"    589
"25"    "000009"    "112036402G"    100 0.970000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "213008200I"    200 1.660000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "213008200N"    150 1.660000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "213008500V1"   53.5    1.930000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "213008500E"    61  1.930000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "213008500R"    56  1.930000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "213008200G"    50  1.660000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "113066592N"    20  5.583000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "199900502N"    321 0.725000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "199900602N"    360 0.680000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "217001100F"    1200    2.036000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "112031102N"    1200    0.198000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "112044602N"    800 0.600000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "112036402N"    800 0.500000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "113066702N"    800 0.600000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "113066602N"    800 0.550000    "2017-01-31 00:00:00+00"    318
"25"    "000009"    "112530780N3"   5000    0.178000    "2017-01-31 00:00:00+00"    318

this is an example of output, in the last column i have the NumeroFattura row and i would to group for this value (in this example i should have two rows for results)

Someone can tell me why i can't group like i would?

So many thanks in advance

Please provide your current results, and the results that you expect, as tabular text. — GMB
– GMB, Commented Mar 13, 2020 at 9:01
Why a group by at all? You are not even using any aggregation functions. Why do you "have to" group by the NumeroFattura column? What aggregation are you trying to do? — user330315
– user330315, Commented Mar 13, 2020 at 9:01
Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… — user330315
– user330315, Commented Mar 13, 2020 at 9:02
You have to write your desired result. Then you can figure it out why you cant group by like that — hoangnh
– hoangnh, Commented Mar 13, 2020 at 9:13

Gordon Linoff · Accepted Answer · 2020-03-13 11:34:05Z

1

If you want one row per "NumeroFattura", then use DISTINCT ON:

SELECT DISTINCT ON ("NumeroFattura") FT."Riferimento1", FT."Riferimento2",
       "CodProdotto", "QuantitaFatturata", "PrezzoUnit", "DataFattura",
       "NumeroFattura", "CodCli"
FROM public.idocuments_as_fatturetestata FT LEFT JOIN
     public.idocuments_as_fatturerighe FR
     ON FT."Riferimento1" = FR."Riferimento1" AND 
        FT."Riferimento2" = FR."Riferimento2"
WHERE FT."CodCli" = '12192' 
ORDER BY "NumeroFattura";

This returns an arbitrary matching row. It is unclear which row you want, but you can add an additional key to the ORDER BY to choose which of them.

answered Mar 13, 2020 at 11:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql query GROUP BY not possible

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related