home SQL tips
blog gallery favorites family barney projects
Up One Level
SQL tips
Art of S/W Dev
Versions
psummary
Buffer format

SQL Tips

Quick statistics

Here's a freebee for all my clients.  It's what I consider my "favorite query", because it proves so useful in so many situations:

select count(*), [field]
from [table]
  where [condition]
group by [field]
order by [field]

Just substitute an appropriate [table] and [field] and you've got some useful statistics.

Notes:

  • Don't type the word table, field, condition, or the square brackets
  • [field] can actually be more than one field, separated by commas - but remember to do it everywhere.
  • Bright students can also include more than one [table], as long as you remember to join all your tables correctly.
  • If you don't want to specify a condition, use "where 1=1".

.  Here's some examples:

db [table] [field] [condition] Results
any sysobjects type 1=1 Report of how many tables (U), system tables (S), stored procedures (P), defaults (D), triggers (TR), views (V) (and so on) are in your current database.
any sysobjects, syscolumns sysobjects.name sysobjects.id = syscolumns.id and sysobjects.type = 'U' Number of columns in each table in the current database
master (6.5 only) syslocks spid 1=1 Which spids (SQL login sessions) have locks, and how many
any iMIS Name MEMBER_TYPE 1=1 How many members you have of each type.  Equivalent to the standard report "member counts by member type".
any iMIS Name STATE_PROVINCE MEMBER_RECORD=1 Breakdown of members by state or province of their preferred mail address
any iMIS Name, Activity Name.ID Name.ID = Activity.ID and ACTIVITY_TYPE = 'DUES' Breakdown of members who have one or more DUES activities, and how many.
 
Google

Search WWW
toomuchblue.com microsoft.com distributed.net

Distributed Computing Download AIMAIM RemoteSend me an Instant MessageAdd me to Your Buddy ListAdd Remote to Your Page

This site has no webmaster.  Please contact the pagewrangler instead.