Thanks again Serge and Don, Don not to sound like an idiot but what would you recommend on adding a unique key. I understand the idea, but didn't want to corrupt tables trying to get it right.
Serge, I knew DISTINCT operated on whole lines, I was counting on that but as you can see, I didn't know about the sum *before* the distinction... Thanks guys, I will try these and see if I can fix it. Regards, Kelly Black Linux was very clearly the answer, but what was the question again? -----Original Message----- From: Serge Paquin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 10:18 AM To: [EMAIL PROTECTED] Subject: Re: sum() using group, and duplicates problems... 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
