0

I have 2 tables with data of invoices like date invoice no. supplier etc. First table differs from another that's why they are creates as separated tables. However both table have identical column which I wanted to UNION and GROUP BY.

When I am doing things with one table everything is OK but when trying to UNION I get separated group by data for example if it is grouped by client and country I get two countries (BE and below also BE) of the same client. Result should provide me only one country with summed VATamount.

I read some articles on the Internet however it does not help me with my problem. So far my syntax looks like this:

SELECT [Faktury - od nas].customerKey, [Faktury - od nas].InvoiceCountry, Sum([Faktury - od nas].VATamount) AS SumaOfVATamount
FROM [Faktury - od nas]
GROUP BY [Faktury - od nas].customerKey, [Faktury - od nas].InvoiceCountry
UNION
SELECT [Faktury - od nich].customerKey, [Faktury - od nich].InvoiceCountry, Sum([Faktury - od nich].VATamount) AS SumaOfVATamount
FROM [Faktury - od nich]
GROUP BY [Faktury - od nich].customerKey, [Faktury - od nich].InvoiceCountry

So far output:

01-72 --- BE --- 125,00
01-72 --- BE --- 180,07
11-54 --- DE --- 206,34
11-54 --- DE --- 100,00
23-65 --- FR --- 23,00
23-65 --- FR --- 100,76

Desired output:

01-72 --- BE --- 305,07
11-54 --- DE --- 306,34
23-65 --- FR --- 123,76

Edit: I was doing some changes in query and found out it stoped grouping as supposed to. Can you help?

SELECT [E100 Key], [Nazwa Klienta], [Kraj], [Okres], Sum(Format(Round([Kwota1],2),'Standard')) AS [Kwota], [Waluta], [Status], [Rozliczenie]

FROM(

SELECT [Faktury - od nas].customerKey AS [E100 Key], [Faktury - od nas].CustomerName AS [Nazwa Klienta], [Faktury - od nas].InvoiceCountry AS Kraj, [Faktury - od nas].Okres AS Okres, Sum([Faktury - od nas].VATamount) AS Kwota1, [Faktury - od nas].Currency AS Waluta, [Faktury - od nas].Status AS Status, [Faktury - od nas].[Transtax invoice No] AS Rozliczenie
FROM [Faktury - od nas]
GROUP BY [Faktury - od nas].customerKey, [Faktury - od nas].CustomerName, [Faktury - od nas].InvoiceCountry, [Faktury - od nas].Okres, [Faktury - od nas].Currency, [Faktury - od nas].Status, [Faktury - od nas].[Transtax invoice No]
HAVING ((([Faktury - od nas].Okres) Is Not Null))

UNION

SELECT [Faktury - od nich].customerKey AS [E100 Key], [Faktury - od nich].CustomerName AS [Nazwa Klienta], [Faktury - od nich].InvoiceCountry AS Kraj, [Faktury - od nich].Okres AS Okres, Sum([Faktury - od nich].VATamount) AS Kwota1, [Faktury - od nich].Currency AS Waluta, [Faktury - od nich].Status AS Status, [Faktury - od nich].[Transtax invoice No] AS Rozliczenie
FROM [Faktury - od nich]
GROUP BY [Faktury - od nich].customerKey, [Faktury - od nich].CustomerName, [Faktury - od nich].InvoiceCountry, [Faktury - od nich].Okres, [Faktury - od nich].Currency, [Faktury - od nich].Status, [Faktury - od nich].[Transtax invoice No]
HAVING ((([Faktury - od nich].Okres) Is Not Null))

)GROUP BY [E100 Key], [Nazwa Klienta], [Kraj], [Okres], [Waluta], [Status], [Rozliczenie];
lowak
  • 1,254
  • 2
  • 18
  • 38
  • 1
    See high rated answer: http://stackoverflow.com/questions/8572821/group-by-with-union-mysql-select-query?rq=1. Should also apply to ACCESS – asdev Sep 22 '15 at 12:23
  • Actually found this: http://stackoverflow.com/questions/26606949/group-by-with-union I tried it yesterday and it didn't work. But tried once again and work great. Sorry for bothering. – lowak Sep 22 '15 at 12:27
  • @asdev Please look read edit. – lowak Oct 07 '15 at 09:35
  • Please rephrase your question. State exactly how the grouping is supposed to be. – asdev Oct 07 '15 at 12:33
  • @asdev Question stays the same. Somehow again I get two ungrouped records as in my example. Pasted my current syntax in the edit. – lowak Oct 07 '15 at 13:46
  • Sorry it is still not clear to me. But: you have following outputs in your SELECT statement `[E100 Key], [Nazwa Klienta], [Kraj], [Okres], Sum(Format(Round([Kwota1],2),'Standard')) AS [Kwota], [Waluta], [Status], [Rozliczenie]` which are also in your GROUP BY. These are 8 columns. But your desired output has only 3 columns like _01-72 --- BE --- 305,07_ . Reduce your SELECT columns – asdev Oct 08 '15 at 08:00
  • @asdev Oh that one... desired output was just a example. I added more columns as I understood the method. Things still stays the same: I have two tables and I want to Union their content. However I get two similar records(does not group with each other) rather than grouped one. Desired output is to group data to get total sum. – lowak Oct 08 '15 at 08:35
  • 1
    Sorry I do not have any ideas or options left, because the SQL looks good to me. Verify, that the data you want to group is exactly the same (means _similar_ exactly the same?) . You could also try to change Sum(...) to sum([Kwota1]) for test purposes to be sure the nested function _Format/Round_ function do not affect anything. What you also can try is to reduce your fields and group by's down to one or two columns. And then add the other fields step by step – asdev Oct 08 '15 at 09:04
  • @asdev Funny... I deleted last column and it worked. Found out last column in 1st table had zero-length strings = "" and 2nd table had Null values. At glance look the same but running query did not give the same results. Thanks for hints how to look at my data :) – lowak Oct 08 '15 at 12:06

1 Answers1

0

Problem solved.
There were 2 issues:

  1. Wrong syntax. Other question helped cope with that: Group by with UNION
  2. Different data in one of columns. 1st table contained zero-length string and 1nd Null values. At glance looks the same but running query revealed differences.

Many thanks to asdev who stayed in touch with my problem and provided strategies to look through.

Community
  • 1
  • 1
lowak
  • 1,254
  • 2
  • 18
  • 38