Group by with UNION

Question

My code is not grouping properly, it still doesn't group [Reden uitstroom2] and [Reden uitstroom3] with [Reden uitstroom1]. The count works properly but its showing duplicates in [Reden Uitstroom1].

For Example:

Reden uitstroom1 = 1x A / 2x B
Reden uitstroom2 = 1x A / 1x B

Aantal Uitstroom 2014 - Reden Uitstroom1
1 - A
1 - A
2 - B
1 - B

Which Should be:

Aantal Uitstroom 2014 - Reden Uitstroom1
2 - A
3 - B

I can't seem to change [Reden Uitstroom1] into [Reden Uitstroom] because then it returns numbers instead of A / B...

SELECT        Count(Hertoetsing.[Reden uitstroom1]) AS [Aantal Uitstroom 2014],
                   (Hertoetsing.[Reden uitstroom1]) AS [Reden Uitstroom1]

FROM          Klantinformatie     

INNER JOIN    Hertoetsing 
    ON        Klantinformatie.KlantID=Hertoetsing.Klantid

WHERE         (((Year(Hertoetsing.[Datum uitstroom1]))=2014))

GROUP BY      Hertoetsing.[Reden uitstroom1]

UNION ALL

SELECT        Count(Hertoetsing.[Reden uitstroom2]) AS [Aantal Uitstroom 2014],
                   (Hertoetsing.[Reden uitstroom2]) AS [Reden Uitstroom1]

FROM          Klantinformatie 

INNER JOIN    Hertoetsing 
    ON        Klantinformatie.KlantID=Hertoetsing.Klantid

WHERE         (((Year(Hertoetsing.[Datum uitstroom2]))=2014))

GROUP BY      Hertoetsing.[Reden uitstroom2]

UNION ALL

SELECT        Count(Hertoetsing.[Reden uitstroom3]) AS [Aantal Uitstroom 2014],
                   (Hertoetsing.[Reden uitstroom3]) AS [Reden Uitstroom1]

FROM          Klantinformatie 

INNER JOIN    Hertoetsing 
    ON        Klantinformatie.KlantID=Hertoetsing.Klantid

WHERE         (((Year(Hertoetsing.[Datum uitstroom3]))=2014))

GROUP BY      Hertoetsing.[Reden uitstroom3];

citizenkong · Accepted Answer · 2014-10-28T12:07:06.763

2

You need to wrap the query in another SELECT, i.e.

SELECT 
    Sum ([Aantal Uitstroom 2014]) AS [Aantal Uitstroom 2014], 
    [Reden Uitstroom]
FROM
(
     [UNION query goes here]

)
GROUP BY [Reden Uitstroom]

Without the SELECT wrapper, all it's doing is appending the results of each individual Count as there is no summation or grouping applied to it.

edited Oct 28 '14 at 12:07

answered Oct 28 '14 at 11:57

citizenkong

679
5
14

Thanks it's working! Just one problem... [Reden Uitstroom] returns numbers, when there are no numbers to be found lol... 1-2-3-4-5-6-7-9 – Hugo Vogels Oct 31 '14 at 08:55
It's taking the numbers from another table, for example 1 is a certain reason in a drop down list and 2 is the next reason etc. – Hugo Vogels Oct 31 '14 at 09:36

Group by with UNION

1 Answers1

Linked