[dba-SQLServer] Count by state

Do you have a table with all the state id codes, names, abbreviations, etc?  I have done this several ways.  
 
1)  Left outer join to sub query .  Below is how I would do it in older oracle.
 
     Select  SID.State_ID,
                SID.State_name,
                q1.My_Count1,
                q1.My_count2
     From State_ID_TBL SID,
              (SELECT mot.State_id,
                           Count(mot.fld1) my_count1,
                           Count(mot.fld2) my_count2
                From my_otherTable  Mot
                Where whatever gets your counter
                group by mot.STATE_ID
                ) q1
      Where  SID.STATE_ID = q1.STATE_ID (+)         '*** oracle left outer join****"
      Group by SID.STATE_ID, SID.STATE_NAME
      ORDER BY SID.STATE_ID
 
if you want zeros then use NVL, DECODE or CASE.  I have examples in A97 if you need that mail me offline at pattioc at yahoo.com 
 
*************************************************************
* Patricia E. O'Connor
* Associate Computer Programmer/Analyst
* OTDA - BDMA
* (W) mailto:Patricia.O'Connor at otda.state.ny.us
* (W) mailto:aa1160 at otda.state.ny.us
***********************************************************


--------------------------------------------------------
This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments.  Please notify the sender immediately by reply e-mail and delete the e-mail from your system. 


________________________________


From: dba-sqlserver-bounces at databaseadvisors.com on behalf of JWColby
Sent: Sat 02/03/2007 9:32 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Count by state



I have a query where I need to count records by state.  I use the groupby
and count and voila.  The problem is that I need every state, whether or not
there is a record (give me a null or a zero), and I need it DOWN the page,
not across the page, i.e. it is going into a spreadsheet as a set of rows,
one row per state.  I will be doing comparisons with other counts where each
count will show up in a different set of states.  I always need all the
states represented in every count.  I know I can do that with the transform,
but that also turns it into columns and I need it kept in rows.

Any suggestions?

John W. Colby
Colby Consulting
www.ColbyConsulting.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com <http://www.databaseadvisors.com/>