The DISTINCT is almost usless when done on a GROUP BY clause. Also DISTINCT operates on the entire row and not just on the field.
ie It will give you DISTINCT measurement, sum(val), pcfver, hour, release. And not just DISTINCT measurement results. Serge. On Wed, 26 Mar 2003 12:00:24 -0600 (CST) Don Read <[EMAIL PROTECTED]> wrote: > > On 26-Mar-2003 Black, Kelly W [PCS] wrote: > > Don > > > > Thanks. I will have to change them and try that. > > > > I had been bouncing back and forth between decimal and > > numeric but wasn't sure which was working better. > > > > I still think it might have to do with my sql query. > > > > Here's an example as you previously mentioned... > > > > SELECT DISTINCT measurement, sum(val), pcfver, hour, release > > FROM pcf WHERE release = curdate()-1 group by measurement, hour; > > > > The ->sql runs fine, but there are hours in which I receive > > duplicated input data. I have filtered as much as I can, but > > was really needing help from the database driver to eliminate > > the rest. > > > > Thanks for your help. > > > > Couple of problems I see in the query: > > 1. curdate()-1 don't do what you think it do: > > mysql> select curdate(), curdate()-27; > +------------+--------------+ > | curdate() | curdate()-27 | > +------------+--------------+ > | 2003-03-26 | 20030299 | > +------------+--------------+ > 1 row in set (0.00 sec) > > --use DATE_SUB(curdate(), INTERVAL 1 DAY) instead. > > 2. If you have duplicates that is going to hammer your results. > The DISTINCT function filters the result set _after_ they're > selected (and SUM'ed). If you have duplicate entries, the sum(val) > will total both rows in the group --probably not what you want. > > A Perl script could help clear up any duplicates. > Then you could add a UNIQUE key to keep 'em out. > > my $0.02. > > Regards, > -- > Don Read [EMAIL PROTECTED] > -- It's always darkest before the dawn. So if you are going to > steal the neighbor's newspaper, that's the time to do it. > (53kr33t w0rdz: sql table query) > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
