[V4RB] GROUP returning too many records

[Stan Busk]

Hi,

I have tried:

  SELECT Sum(Amount)
    FROM Accs
    WHERE Type1='3' OR Type2='3'
    GROUP BY Type1, Type2
    ORDER BY Type1, Type2

and

  SELECT Sum(Amount)
    FROM Accs
    WHERE Type1='3' OR Type2='3'
    ORDER BY Type1, Type2
    GROUP BY Type1, Type2

Both return a NIL cursor, anyway I really don't understand how a ORDER 
BY can help here when two group fields. About the Account field, it is 
a VDouble and I can't change it as I need doubles. Changing any other 
type will be very dangerous for the whole app integrity.

By the way, it works fine if type is anything else that 3, and type 3 
is the most common type in my database.

Stan

On Thursday, June 5, 2003, at 01:42  AM, Gunnar Swan wrote:

>
> 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
>
>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>
>