Group By With Union

[MattSmithProg]

Hi All,

I have probably a really easy question but I can't seem to nut it out.

I have a Union query like this.

SELECT UserId, Site, NAT, Count(NAT) As Total

FROM PTAPP_ACR_MAIN

WHERE Site = 'HOB'

GROUP BY 1, 2, 3

UNION ALL

(SELECT UserId, Site, NAT, Count(Nat) As Total

FROM PTAPP_ACR_Inactive

WHERE Site = 'HOB'

Group By 1, 2, 3)

ORDER BY 1, 2, 3
;

However I want to group the results from the two tables together. Ie I get the same UserId, Site, Nat twice in the results but each set is the total from each table.

I would like to use a group by on the whole result query but am not sure how.

Any help would be greatly appreciated.

Thanks

Matt Smith No two nulls are the same

 

[cmmrfrds]

Is this what you are asking? From BOL.

By default, the UNION operator removes duplicate rows from the result set. If you use ALL, all rows are included in the results and duplicates are not removed.

 

[MattSmithProg]

No not quite.

I am grouping based on UserId and Nat with a count for Nat.

Say UserId = UBD2O has 3 unique records with the same NAT on the PTAPP_ACR_Main table and 5 unique records on the PTAPP_ACR_Inactive table.

What is returned is this

UBD2O DEMO1 3
UBD2O DEMO1 5

What I want is

UBD2O DEMO1 8

Thanks for your help so far.

Matt Smith No two nulls are the same

 

[MattSmithProg]

I actually solved it.

I used this

SELECT UserId, Site, Nat, Count(NAT) AS Total

FROM (

SELECT UserId, Site, NAT

FROM PTAPP_ACR_MAIN

WHERE Site = 'HOB'

UNION ALL

SELECT UserId, Site, NAT

FROM PTAPP_ACR_Inactive

WHERE Site = 'HOB'

)

AS TEMP1

GROUP BY UserId, Site, NAT

ORDER BY 1, 2, 3
;

Thanks for all those thought about it.

Matt No two nulls are the same