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]
group by [field]
order by [field]
Just substitute an appropriate [table] and [field] and you've got some useful
- 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:
||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.
||sysobjects.id = syscolumns.id and
sysobjects.type = 'U'
||Number of columns in each table in
the current database
|master (6.5 only)
||Which spids (SQL login sessions)
have locks, and how many
||How many members you have of each
type. Equivalent to the standard report "member counts by
||Breakdown of members by state or
province of their preferred mail address
||Name.ID = Activity.ID and
ACTIVITY_TYPE = 'DUES'
||Breakdown of members who have one
or more DUES activities, and how many.