Posted By

gembry on 01/21/09


Tagged

sql server all UNION BY group


Versions (?)

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.

  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;

Report this snippet  

You need to login to post a comment.