Don This is more help than I ever anticipated, and I certainly thank you very much. Let me digest / re-program this. If there's anything I can do you for, just let me know.
Thanks lots! Regards, Kelly W. Black Linux was very clearly the answer, but what was the question again? -----Original Message----- From: Don Read [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 8:44 PM To: Black, Kelly W [PCS] Cc: [EMAIL PROTECTED] Subject: RE: sum() using group, and duplicates problems... On 26-Mar-2003 Black, Kelly W [PCS] wrote: > 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. > It's tough to say without the context. So I'll make some assumptions and guesses. Kelly, you get to figure out if they are valid for you. Let's look at the query: SELECT DISTINCT measurement, sum(val), pcfver, hour, release FROM pcf WHERE release = curdate()-1 group by measurement, hour; --- I'll assume 'release' is a DATE and 'hour' is a TINYINT UNSIGNED, meaning the hour of 'release' day when the entry was recorded. I'll further assume 'measurement' is a discrete measurement sensor. Example: a thermocouple at the bottom of an exhaust stack is a different measurement # than the TC at the top of the stack. And 'pcfvar' is some nominal or ideal value (but it could be some max/min acceptable). The 'val' field is, of course, your independent y-variable. --- We've already discussed/fixed the 'curdate()-1' error. And since yure going to constrain the table to UNIQUE entries, let's get rid of the DISTINCT function. So, you're left with the X-axis (non-independent) variables: 'measurement', 'release', 'hour', & 'pcfvar'. The 'measurement' is a no-brainer: the temp at the bottom of the chimney has nothing to do with the top (in the database sense). 'release' ? another no-brainer. You're reporting by date. So far the index is : UNIQUE KEY mr (measurement, release); 'pcfvar' is an iffy. It might change by the date or hour. Example: aircraft taking off before 7am must be quieter than 90dB, but can hit 105 dB afterwards (yeah, I made up the numbers). UNIQUE KEY mrp (measurement, release, pcfvar); 'hour' gets a bit more tricky. If "pinkware" (humans) are recording the data, you've got to consider the case of entries at :01 and then :59 in the same hour ... And which is more "valid" ? "INSERT IGNORE INTO ... or REPLACE INTO ..." ? Or you could allow both readings and AVG() indstead of SUM() ... Depending on what you'll allow, a full constraint would be: UNIQUE KEY mrph (measurement, release, pcfvar, hour); --- Kelly. You owe me two virtual beers. Shiner Bock or Miller High-Life tall-boys will be fine. Invoice to follow. 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]
