<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal>I am trying to use fpSQL against a FilePro database file to get various group totals, using COUNT and GROUP BY. But there seems to be some interaction between the use of GROUP BY and case sensitivity. As a simple example, here is part of the resulting list when I query for records in Washington state (the query is “select @5, count(*) from myDB where @5 = ‘WA’ group by @5” -- and field 5 is the two-character state code):<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>WA 6<o:p></o:p></p><p class=MsoNormal>Wa 1<o:p></o:p></p><p class=MsoNormal>WA 11<o:p></o:p></p><p class=MsoNormal>Wa 2<o:p></o:p></p><p class=MsoNormal>WA 9<o:p></o:p></p><p class=MsoNormal> etc., etc.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Every time it encounters a lower case letter in the state field, it starts a new group, and then starts yet another new group when it hits the next record without any lower case letters. And then it is unable to group ALL the upper case fields together, and ALL the lower case fields together. I know that in this simple example, I can omit the GROUP BY, and fpSQL will indeed return the correct case-insensitive total, i.e., a single total for all of Washington state. However, usually I am not looking for only one group, but all the many groupings within a given field, so the GROUP BY is needed to form the various groups.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Any ideas? Does fpSQL have any settings with respect to case sensitivity?<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Thanks,<o:p></o:p></p><p class=MsoNormal>Rich<o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p></div></body></html>