| |
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. |
|