Group by the union

[janko.klemensek]

Can I Group by the Union in one query or must I write two queries for
this (one for union and second for group by)?

[BillCo]

On May 20, 7:28 pm, [e-mail] wrote:

Can I Group by the Union in one query or must I write two queries for
this (one for union and second for group by)?

you can sub-query the union query:

select * from
(select a, b, c from tblx
union all
select a, b, c from tbly) as qryUnion
group by a, b, c

however, if the point of your grouping is to remove duplicate rows,
then use "union" rather then "union all", and this will be automatic.

[janko.klemensek]

I have one big query:

SELECT ID_Artikel, Kolicina

FROM

(SELECT tblDokumentiDetails.ID_Artikel, tblDokumentiDetails.Kolicina
FROM (tblDokumenti INNER JOIN tblDokumentiDetails ON
tblDokumenti.ID_Dokument = tblDokumentiDetails.ID_Dokument) INNER JOIN
[SELECT tblDokumentiDetails.ID_Artikel, Max(tblDokumenti.Datum) AS
ZadnjiDatum
FROM tblDokumenti INNER JOIN tblDokumentiDetails ON
tblDokumenti.ID_Dokument = tblDokumentiDetails.ID_Dokument
WHERE tblDokumenti.ID_Vrsta=3 And tblDokumenti.Storniran=false And
tblDokumenti.Zakljucen=true
GROUP BY tblDokumentiDetails.ID_Artikel]. AS ZadnjaInventura ON
(tblDokumentiDetails.ID_Artikel = ZadnjaInventura.ID_Artikel) AND
(ZadnjaInventura.ZadnjiDatum = tblDokumenti.Datum))

UNION ALL

(SELECT tblDokumentiDetails.ID_Artikel, tblDokumentiDetails.Kolicina
FROM (tblDokumenti INNER JOIN tblDokumentiDetails ON
tblDokumenti.ID_Dokument = tblDokumentiDetails.ID_Dokument) INNER JOIN
[SELECT tblDokumentiDetails.ID_Artikel, Max(tblDokumenti.Datum) AS
ZadnjiDatum
FROM tblDokumenti INNER JOIN tblDokumentiDetails ON
tblDokumenti.ID_Dokument = tblDokumentiDetails.ID_Dokument
WHERE (((tblDokumenti.ID_Vrsta)=3))
GROUP BY tblDokumentiDetails.ID_Artikel]. AS ZadnjaInventura ON
(ZadnjaInventura.ZadnjiDatum < tblDokumenti.Datum) AND
(tblDokumentiDetails.ID_Artikel = ZadnjaInventura.ID_Artikel)
WHERE tblDokumenti.ID_Vrsta = 1 And tblDokumenti.Storniran=false And
tblDokumenti.Zakljucen=true)

UNION ALL

(SELECT tblDokumentiDetails.ID_Artikel, - tblDokumentiDetails.Kolicina
AS Kolicina
FROM (tblDokumenti INNER JOIN tblDokumentiDetails ON
tblDokumenti.ID_Dokument = tblDokumentiDetails.ID_Dokument) INNER JOIN
[SELECT tblDokumentiDetails.ID_Artikel, Max(tblDokumenti.Datum) AS
ZadnjiDatum
FROM tblDokumenti INNER JOIN tblDokumentiDetails ON
tblDokumenti.ID_Dokument = tblDokumentiDetails.ID_Dokument
WHERE (((tblDokumenti.ID_Vrsta)=3))
GROUP BY tblDokumentiDetails.ID_Artikel]. AS ZadnjaInventura ON
(ZadnjaInventura.ZadnjiDatum < tblDokumenti.Datum) AND
(tblDokumentiDetails.ID_Artikel = ZadnjaInventura.ID_Artikel)
WHERE tblDokumenti.ID_Vrsta = 2 And tblDokumenti.Storniran=false And
tblDokumenti.Zakljucen=true);

That works OK. But If I want to group this result like this:

SELECT ID_Artikel

FROM

( BIG QUERY AFTER FROM) AS test
GROUP BY test.ID_Artikel

I get Syntax error in JOIN operation :(

BillCo je napisal:

On May 20, 7:28 pm, [e-mail] wrote:

Can I Group by the Union in one query or must I write two queries for
this (one for union and second for group by)?

you can sub-query the union query:

select * from
(select a, b, c from tblx
union all
select a, b, c from tbly) as qryUnion
group by a, b, c

however, if the point of your grouping is to remove duplicate rows,
then use "union" rather then "union all", and this will be automatic.

[BillCo]

i would have expected a different error there: Kolicina is not part of
an expression or a group by clause
....try including it in the group by section. failing that, i don't
know. when you write the query like you described there without the
group by, you should be able to view the resulting union sub-query as
a table in the query editor. try adding the grouping here and see if
it works.
Personally, I like to save the queries seperately and join them in a
master query - it makes debugging and editing easier

[janko.klemensek]

tnx for advice and your time

BillCo je napisal:

i would have expected a different error there: Kolicina is not part of
an expression or a group by clause
...try including it in the group by section. failing that, i don't
know. when you write the query like you described there without the
group by, you should be able to view the resulting union sub-query as
a table in the query editor. try adding the grouping here and see if
it works.
Personally, I like to save the queries seperately and join them in a
master query - it makes debugging and editing easier