Advanced Search

Return to Snippet

Revision: 11049
at January 21, 2009 20:29 by gembry
Initial Code 
EXAMPLE 1 -----
SELECT    Activity
        , COUNT (registrationID) AS totals
FROM
          (SELECT
                    CASE
                              WHEN r1.Activity0403_Winner <> 'Spa (50 minutes)'
                              THEN r1.Activity0403_Winner
                              ELSE r1.Activity0403_Winner + ' - ' + r1.Activity0403_WinnerSpaChoice + ' ~ ' + r1.Activity0403_WinnerSpaTime
                    END AS Activity
                  , r1.registrationID
          FROM      Registrations r1
          WHERE     r1.Activity0403_Winner IS NOT NULL
                AND
                    r1.Activity0403_Winner <> 'No Activity'
          
          UNION ALL
          
          SELECT
                    CASE
                              WHEN r2.Activity0403_Guest <> 'Spa (50 minutes)'
                              THEN r2.Activity0403_Guest
                              ELSE r2.Activity0403_Guest + ' - ' + r2.Activity0403_GuestSpaChoice + ' ~ ' + r2.Activity0403_GuestSpaTime
                    END AS Activity
                  , r2.registrationID
          FROM      Registrations r2
          WHERE     r2.Activity0403_Guest IS NOT NULL
                AND
                    r2.Activity0403_Guest <> 'No Activity'
          ) AS X (Activity, registrationID)
GROUP BY  X.Activity
ORDER BY  X.Activity;


EXAMPLE 2 -----
SELECT X.emp_id, COUNT(project_id) AS project_tot 
FROM (SELECT emp_id, project_id 
FROM DB1.Table1 
UNION 
SELECT emp_id, project_id 
FROM DB2.Table1) 
AS X(emp_id, project_id) 
GROUP BY X.emp_id;
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
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.

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Getting Count's with a UNION of two tables

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
sql, server

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL