Alice, you asked
>I have a table with some 6300 records. I'm trying to use a
SQL
>Select to obtain a count of how many discrete values the column
>contains.
>Select to obtain a count of how many discrete values the column
>contains.
You're in luck. The SQL language supports grouping and counting
operations. For example if you have a relational database table
containing facts abour clients and residential states
then it's easy with SQL to count how many clients reside in each
state.
Specifically, if your MapInfo table is called Clients and
contains the two columns ClientName and
StateOfResidence then use the query:
Select StateOfResidence,
Count(*)
From Clients
Group By StateOfResidence
Order by
StateOfResidence
Into
ClientCountByState
This query creates a result set table called
ClientCountByState with two columns State of Residence
and Count. The table contains one row for each
state and lists the states and their client counts in ascending order of state
code.
The Count(*) and Group By terms together
lead to this result. Count(*) means include a numeric column called
"Count" in the output and populate it with the counts of the
numbers of rows in each group as determined by the Group By.
Obviously, to do this the SQL engine must perform the Group By
operation before performing the Count(*)
operation, even thought you have to write the statement in the sequence
shown.
To make a "premiership table" with the most populous state
first, try:
Select
StateOfResidence,Count(*)
From Clients
Group By StateOfResidence
Order by Col2 Desc
From Clients
Group By StateOfResidence
Order by Col2 Desc
Into
ImportantStates
Order by Col2 Desc means sort the rows of the result set
table ImportantStates in descending order on the Count column,
which places the row with the biggest count first. To execute this query,
the SQL engine performs the operations in the order Group By
StateOfResidence, then Count (*) and finally
Order by Col2 despite the way you write it.
It's this out-of-sequence business that makes SQL such a b*** for students
in CS101 :-). Honours question: why can't you write Count(Clients) instead
of Count(*)?
David M
Haycraft
Phone/Fax: 61 + 2 + 6231 8104
Information Analysis Assocs P/L Mobile: 0412 001 134
ACN 085 516 105 Email: [EMAIL PROTECTED]
1 Cumming Place, Wanniassa Web: www.acslink.aone.net.au/actaa/iaa.htm
A.C.T. 2903, Australia A MapInfo Partner
Information Analysis Assocs P/L Mobile: 0412 001 134
ACN 085 516 105 Email: [EMAIL PROTECTED]
1 Cumming Place, Wanniassa Web: www.acslink.aone.net.au/actaa/iaa.htm
A.C.T. 2903, Australia A MapInfo Partner
