[dba-SQLServer] Count by state

Try this...

SELECT
'Total' = COUNT(*),
'AK' = COUNT(CASE WHEN [State] = 'AK' THEN [State] ELSE 0 END),
'AZ' = COUNT(CASE WHEN [State] = 'AZ' THEN [State] ELSE 0 END),
....
FROM MyStateTable (nolock)
GROUP BY [State]
ORDER BY [State] 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Saturday, February 03, 2007 6: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

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.411 / Virus Database: 268.17.24/668 - Release Date: 2/4/2007