Thanks for the responses (DPC, Peter, and Patrick). The best technique for me was from Peter, who suggested using the int() function to convert from logical to numeric. This led to the following expression:
int(popden>=0)+int(popden>=1000)+int(popden>=5000)+int(popden>=10000) (There is no reason to multiply by one, as int(TRUE) = 1.) My entire SQL statement, which summed the population for the various density classes, was: Select sum(pop), int(popden>=0)+int(popden>=1000)+int(popden>=5000)+int(popden>=10000) "DenClass" from DenTable group by DenClass order by DenClass into DenClassTable This yields a table (DenClassTable) like this: Sum(pop) DenClass 1,042,583 1 2,567,644 2 1,257,268 3 1,443,494 4 Of course, I still want more. Is there any way to get SQL to report the percentage each sum(pop) represents of the total population (in this case, 6,310,989), like this: Sum(pop) DenClass Percent 1,042,583 1 17 2,567,644 2 41 1,257,268 3 20 1,443,494 4 23 I tried making another query to get the total (by doing a sum(pop) grouped on "1"), and joining it to the DenClassTable, but MapInfo doesn't let you do join queries on anything but base tables. Any work-arounds? As earlier, I know that I could do this in MapBasic, but I'm looking for something more dynamic (and, of course, more elegant). My original question was: ----Original Message Follows---- From: "K Miller" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: MI-L SQL query to group data on classifications Date: Mon, 08 Apr 2002 16:50:18 -0400 Greetings, I am trying to write a SQL query to group my data (and provide group sums) on specific categories of a particular field. In particular, I'm trying to sum population by population density categories (< 1,000 people per square mile, 1,000 - 5,000 ppsm, 5,000 - 10,000 ppsm, > 10,000 ppsm). I thought I could use an expression in the "select columns" portion of the SQL query, but I am coming up against a brick wall. I tried using an expression such as (popden>=0)*1+(popden>=1000)*1+(popden>=5000)*1+(popden>=10000)*1 in the hopes that it would give me "1" for up to 1,000, "2" for 1,000 to 5,000, "3" for 5,000 to 10,000, and "4" for above 10,000, but MI complains about a datatype mismatch (it doesn't treat a logical as a number). Any suggestions? Ways to convert between a "True" and a 1? Other ways to go about this? I'd rather not create a new column in the table and set it's value. I'd like the solution to be more dynamic than that. For now, my only hope is an expression in the following form: str$(popden>=0) & str$(popden>=1000) & str$(popden>=5000) & str$(popden>=10000) which gives me "TFFF" for up to 1,000, "TTFF" for 1,000 to 5,000, "TTTF" for 5,000 to 10,000, and "TTTT" for above 10,000. Not my ideal solution, but at least it works. Thanks in advance for your help, and I'll SUM any other solutions I might come across. ------------------------------------------------------------------------ Keith Miller Principal Planner: GIS and Modeling North Jersey Transportation Planning Authority, Inc. One Newark Center, 17th floor Newark, NJ 07102 973-639-8444 [EMAIL PROTECTED] _________________________________________________________________ Join the world’s largest e-mail service with MSN Hotmail. http://www.hotmail.com --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
