By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,347 Members | 2,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,347 IT Pros & Developers. It's quick & easy.

Group by the union

P: n/a
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)?

May 20 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On May 20, 7:28 pm, janko.klemen...@gmail.com 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.

May 21 '07 #2

P: n/a
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, janko.klemen...@gmail.com 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.
May 21 '07 #3

P: n/a

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
May 22 '07 #4

P: n/a
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
May 23 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.