[thelist] SQL - Group by peculiarity in Sybase

[Ken Schaefer]

Hi there,


: When I alter the query to:
: 
: SELECT taktar,taktut, sum(taktut) as toplam FROM p_taksit 
: WHERE (necessary filters)
: 
: According to my logic I should get.
: 
: Apr 14 2005 12:00 AM	-  95.38	-  95.38


Actually the last column should contain the sum of all the records in the
table (after a filter has been applied) unless you do a GROUP BY, would be my
understanding of how this would work.

Now, as to the filter problem - if you use ' instead of " around your dates,
does the problem go away? The only thing I can think of is that the database
is not having the same interpretation of your dates that you think it does.
But that doesn't explain why the query works in your first example, and not
your second. 

I'm a little stumped - maybe asking in a specialist SyBase forum?

Cheers
Ken

--
www.adOpenStatic.com/cs/blogs/ken/ 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of VOLKAN ÖZÇELIK
: Subject: Re: [thelist] SQL - Group by peculiarity in Sybase
: 
: > What are you doing in "(necessary filters)"? The only thing that comes
: > to mind is that if you are doing some kind of result limiting (like
: > MySQL's LIMIT clause), then the database might be incorrectly using
: > the table's stored sum in the system catelog. But, if you're offering
: > it a date range in the WHERE clause, etc., it shouldn't be using that
: > stored value.
: >
: 
: nothing special in the where clause no set rowcounts, no limits,
: (btw sybase does not support limit at all, it has a set rowcount
: directive to deal with it.)
: 
: Here is the full query:
: 
: SELECT taktar,taktut, sum(taktut) as toplam FROM p_taksit
: WHERE taktar between "2005-04-14" and "2005-04-16"
: AND taktut > 0
: 
: nothing really special about the where clause as well.
: I hadn't given the entire SQL to keep things simple.
: 
: > Still, aside from serious deviation in Sybase from SQl standard, I
: > don't see us being able to help without seeing the WHERE clause.
: 
: The sql is dead simple. That's what makes me conclude that
:  there exists such a deviation. However sybase is a well known and
: commercial database, so imho it will not deviate from standards this
: much.
: 
: Have anyone coincided a similar situation? Or am I alone?
: Do you think that while the DB tries to "optimize" things, it screws
: them up even more.
: 
: Does it require all aggragete columns be listed in the group by clause
: (when I use group by, I receive a single row again)
: 
: What can the reason be?
: any ideas?
: 
: I will rtfm carefully (the product is shipped with tons of explanatory
: pdf's) I'll inform you if I find anything. In the mean time, your
: assistance will be HIGHLY appreciated.
: I do not want to lose my brain in this SQL dilemma :)
: 
: Cheers
: Volkan