[V4RB] GROUP returning too many records

[Gunnar Swan]

Without ORDER BY, I was unable to get GROUP BY to work.
Try with ORDER BY first, then with GROUP BY first.
Then try to change the data type of the field.


6/4/03 4:38:31 PM, Stan Busk <maxprog at mac.com> wrote:

>Hi,
>
>I have tried every possible combinations with no success. I am really 
>scared about this because it means the GROUP command is not secure at 
>all and can generate random results. It works everywhere but in one 
>place in all my project. The ORDER BY in this particular case just 
>create NIL cursors wherever I write it. It is the very first time I 
>have no explanation of what's up with a line of code. I use to write 
>very extensive and complex queries without problems but today during 5 
>hours I have been completely stuck to a single SQL command that simply 
>doesn't work but it should. I finally think I have discovered a bug.
>
>Stan
>
>>
>> Check the data type of field "Amount"
>> Experiment with different data types on this field.
>> If I remember correctly, Aggregate functions did not work with some 
>> field types.
>> Again, my feeble memory, but I remember we had to change the field 
>> type to get things to sum correctly.
>>
>> Also, I think you will need to use ORDER BY, and sort records 
>> according to GROUP.
>> Other databases all you to skip ORDER BY .. it's implied. Valentina ( 
>> I think ) requires ORDER BY.
>>
>> Also, I think it was particular ... it wanted ORDER BY first in SQL, 
>> then GROUP BY.
>> In any case, once we went through these items, it worked perfect.
>>
>> Gunnar.
>>
>>
>> 6/4/03 4:00:52 PM, Stan Busk <maxprog at mac.com> wrote:
>>
>>> I meant
>>>
>>>  SELECT Sum(Amount),Type1,Type2
>>>    FROM Accs
>>>    WHERE Type1='3' OR Type2='3'
>>>    GROUP BY Type1, Type2
>>>
>>> Sorry,
>>> ~/Stan
>>>
>>>>  SELECT Sum(Amount)
>>>>    FROM Accs, Type1, Type2
>>>>    WHERE Type1='3' OR Type2='3'
>>>>    GROUP BY Type1, Type2
>>>
>>> _______________________________________________
>>> Valentina mailing list
>>> Valentina at lists.macserve.net
>>> http://lists.macserve.net/mailman/listinfo/valentina
>>>
>>
>>
>> Best Regards,
>> Gunnar Swan
>> http://www.PracticeToPass.Com
>>
>>
>>
>> _______________________________________________
>> Valentina mailing list
>> Valentina at lists.macserve.net
>> http://lists.macserve.net/mailman/listinfo/valentina
>>
>>
>
>_______________________________________________
>Valentina mailing list
>Valentina at lists.macserve.net
>http://lists.macserve.net/mailman/listinfo/valentina
>


Best Regards,
Gunnar Swan
http://www.PracticeToPass.Com