# Posted By

gembry on 01/21/09

# Statistics

Viewed 175 times
Favorited by 0 user(s)

# Getting Count's with a UNION of two tables

/ Published in: SQL

I was having the hardest time getting this to work. It's a collection of various items. For whatever reason, I needed to include the CASE statement due to NULL's coming back. If this helps you out... go for it.

Copy this code and paste it in your HTML
1. EXAMPLE 1 -----
2. SELECT Activity
3. , COUNT (registrationID) AS totals
4. FROM
5. (SELECT
6. CASE
7. WHEN r1.Activity0403_Winner <> 'Spa (50 minutes)'
8. THEN r1.Activity0403_Winner
9. ELSE r1.Activity0403_Winner + ' - ' + r1.Activity0403_WinnerSpaChoice + ' ~ ' + r1.Activity0403_WinnerSpaTime
10. END AS Activity
11. , r1.registrationID
12. FROM Registrations r1
13. WHERE r1.Activity0403_Winner IS NOT NULL
14. AND
15. r1.Activity0403_Winner <> 'No Activity'
16.
17. UNION ALL
18.
19. SELECT
20. CASE
21. WHEN r2.Activity0403_Guest <> 'Spa (50 minutes)'
22. THEN r2.Activity0403_Guest
23. ELSE r2.Activity0403_Guest + ' - ' + r2.Activity0403_GuestSpaChoice + ' ~ ' + r2.Activity0403_GuestSpaTime
24. END AS Activity
25. , r2.registrationID
26. FROM Registrations r2
27. WHERE r2.Activity0403_Guest IS NOT NULL
28. AND
29. r2.Activity0403_Guest <> 'No Activity'
30. ) AS X (Activity, registrationID)
31. GROUP BY X.Activity
32. ORDER BY X.Activity;
33.
34.
35. EXAMPLE 2 -----
36. SELECT X.emp_id, COUNT(project_id) AS project_tot
37. FROM (SELECT emp_id, project_id
38. FROM DB1.Table1
39. UNION
40. SELECT emp_id, project_id
41. FROM DB2.Table1)
42. AS X(emp_id, project_id)
43. GROUP BY X.emp_id;