Trouble with Average

I have a log file that captures data from various sensors every minute that we use to draws graphs on our website. The table looks like this: CREATE TABLE `log` ( `id` int(14) NOT NULL auto_increment, `VarName` varchar(255) NOT NULL, `TimeString` datetime NOT NULL, `VarValue`

Getting a Value and an Average Value of Previous 20 Records in One Query

I have been struggling with this issue most of the day. I can get the result I need by using 2 queries, but that takes way too long. I'm trying to see if there is a way to get the same result within a single query. Here's the table CREATE TABLE `log` ( `id` int(14) NOT NULL auto_increment,

Join Suddenly Failing

I have a website that gets used once a year for a soccer tournament. It has been working fine since 2006. No script changes since it was last used in 2009. All of a sudden the following script started throwing an error. SELECT contactinfo.contactdata, contactinfo.ContactID FROM team, person,

Re: Join Suddenly Failing

this document in error and that any use, disclosure, dissemination, distribution, or copying of this message is prohibited. If you have received this communication in error, please notify the sender immediately by e-mail, and delete the original message -Original Message- From: Albert Padley

Slow seach - Possible better query

I have a query that works and returns the correct results. However, it is very slow ( 6-12 seconds on 5000 row table). Since this table will grow to several hundred thousand rows very shortly, I am worried. Here is the query that works: SELECT id, subject, updated FROM mrldisc WHERE (id IN

Re: Slow seach - Possible better query

Thanks, Dan On 10/31/06, Albert Padley [EMAIL PROTECTED] wrote: I have a query that works and returns the correct results. However, it is very slow ( 6-12 seconds on 5000 row table). Since this table will grow to several hundred thousand rows very shortly, I am worried. Here is the query

Re: Slow seach - Possible better query PROBLEM SOLVED

. Al On Oct 31, 2006, at 4:01 PM, Albert Padley wrote: Dan, On Oct 31, 2006, at 3:41 PM, Dan Buettner wrote: Albert, it seems like the first query could be simplified, like so: SELECT id, subject, updated FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) ORDER BY updated DESC LIMIT

Sort Problem

I have the following query that has worked fine for displaying standings for a soccer league. SELECT * FROM standings WHERE division = 'BU10' AND pool = '1' ORDER BY tpts DESC, spts DESC, w DESC, ga ASC, team_number ASC As I said, works fine. Now, however, the league wants a slightly

Re: Sort Problem

On Sep 15, 2006, at 12:56 PM, Chris W wrote: Albert Padley wrote: I have the following query that has worked fine for displaying standings for a soccer league. SELECT * FROM standings WHERE division = 'BU10' AND pool = '1' ORDER BY tpts DESC, spts DESC, w DESC, ga ASC, team_number ASC

Re: Problem With Join Syntax

Keith, I agree that would be a good option to change the table. Unfortunately, that's not an option at this point. I don't control the schema. Thanks for the suggestion anyway. Albert Padley On Jun 14, 2006, at 3:59 PM, Keith Roberts wrote: Hi Chris. I cannot see how it can be done

Problem With Join Syntax

to return all the `values` of a single userid in a single row. I've checked my books, the manual and tried every type of join I can think of without success. I'd appreciate some direction. Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql

Re: Problem With Join Syntax

, GROUP_CONCAT(value) GROUP BY userid HTH, Dan Albert Padley wrote: I have the following table schema in MySQL 4.1.18 which I didn't create, but have to work with. CREATE TABLE `phplog_userinput` ( `id` int(11) NOT NULL auto_increment, `inputfieldid` int(11) NOT NULL default '0', `userid` int(11

Re: Totals Across Multiple Records

Sol and Peter, Thanks for your feedback. Both of your suggestions got me going in the right direction and I was able to solve the problem using temporary tables and left joins. Thanks again. Albert Padley On Jul 23, 2005, at 11:00 AM, sol beach wrote: create table count_temp1 select

Totals Across Multiple Records

the table structure because I have simplified it here for finding a solution. The above columns are part of a much larger table that is currently in use for other purposes. Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe

Generating Soccer Standings

. However, I don't know enough to even know where to start looking. How do I accomplish this? Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Generating Soccer Standings

the =3,1,0 in the first line and the = 'H',1.0 in the second line. Thanks. Albert Padley On Jul 11, 2005, at 1:13 PM, [EMAIL PROTECTED] wrote: SELECT `coach` , sum(`standingpts`) as standings , count(`game_id`) as games , sum(if(`standingpts`=3,1,0)) as wins

Re: Generating Soccer Standings

. How would I find the total goals scored against a team in the context of the query below? Thanks. Albert Padley On Jul 11, 2005, at 1:13 PM, [EMAIL PROTECTED] wrote: SELECT `coach` , sum(`standingpts`) as standings , count(`game_id`) as games , sum(if(`standingpts

JOIN Problem

I have the following 2 tables: CREATE TABLE `division_info` ( `id` int(11) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `spots` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `division` (`division`), KEY `spots` (`spots`) ) TYPE=MyISAM CREATE TABLE

Re: JOIN Problem

On Feb 17, 2005, at 10:34 AM, [EMAIL PROTECTED] wrote: Albert Padley [EMAIL PROTECTED] wrote on 02/17/2005 12:08:31 PM: I have the following 2 tables: CREATE TABLE `division_info` (    `id` int(11) NOT NULL auto_increment,    `division` varchar(50) NOT NULL default '',    `spots` int

Re: Sort Problem

Nothing? Not even a You're out of luck? Thanks. Albert On Oct 21, 2004, at 9:48 PM, Albert Padley wrote: I've inherited a problem for a youth soccer league. Their standings are computed by adding 3 columns (game_pts, ref_pts and adjust_ref_pts) together. However, the sum of ref_pts plus

Re: Sort Problem SOLVED

+ adjust_ref_pts END AS total_ref_pts FROM points WHERE division = 'U14B' ORDER BY total_pts DESC Albert Padley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Nothing? Not even a You're out of luck? Thanks. Albert On Oct 21, 2004, at 9:48 PM, Albert Padley wrote: I've inherited a problem

Re: Sort Problem

information instead of manipulating. If this is a special case I can see is necessary. so if the values for game_pts, ref_pts, adjust_ref_pts are 10, 9, 7 can it take its true add of 26 or should it be 25? Then why not have the right values to begin with. Just curious. Juan Albert Padley on 10

Sort Problem

. Unfortunately, I don't have the luxury of being able to change the table structure. Using mysql 4.0.18 Thanks. Albert Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]