Average Length

Best Average
OK. Bit of a maths question (at which I am useless)

Basically i've got a table with avg_rating and users.

avg_rating is the average rating of a specific id and users is that amount of people who have voted. I'm trying to sort the list by the best rating. However, if 50 users have voted an average of 2 for one id but only one user has voted 5 for another id, this id would be 1st on the list.

Is there a way I can sort the table by the best avg compared to the total of users that have voted?

---

Average Age
I have a dating script running which uses MySQL to store the users and their information. Now that I have some users, I would like to create a PHP file to show some site statistics. Actually I would like to get the average age of the users that are stored in the database, for example something like

Average age: (At this point I would just like the average age, for example 27.33)

Is this possible in some way?

---

Average Age
I got this players table and I do want to list its average age...

SELECT AVG(YEAR(SUBDATE(CURDATE(), TO_DAYS(birth)))) FROM players

This works fine for me BUT, it doesn't on my server though it isn't 4.1.XX as it is on my computer at home...

---

7 Day Average
I don't know if this can be done but I'm looking to write a query that selects the LAST 7 records the and gives me an AVERAGE.
Basically I have a table which logs the number of hits each day, I want to select the last 7 and see what my average is. How do i write something like this?
there is is only 2 fields in the counter table called date and counter.

---

Returning A One Value Average
I'm using PHP and MySQL, and am trying to return an average (songle value).
My code isn't working, and I'm getting confused between the mysql_fetch, here's my code:

$average = "SELECT AVG(overall) as overall, AVG(gameplay) as gameplay,
AVG(graphics) as graphics, AVG(sound) as sound FROM VIDEO_GAME_DETAIL
WHERE name=" .$row['name'];
$result = mysql_query($average);
$rating = mysql_fetch_array($result);

// down in an HTML table
echo "<td>" .$rating['overall']. "</td>";


$row['name'] is the name of a PC Game from the VIDEO_GAME table. In the VIDEO_GAME_DETAIL table I have reviews for many games, with ratings. My goal here is to average the different ratings (overall, gameplay, sounds and graphics) for a one game, and stick them with the main page that lists the games (1 row per game).

I get this following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /Users/Marconi/Grad/lleccia/www/ISYG250/proj4/reviews.php on line 54

line 54 is $rating = mysql_fetch_array($result);

---

Running Average
How can I get mysql to do a running average of a given field using dates
from another column so that the average for each element is for a given time
period rather than from the 5 rows next to the element?

---

Average From Two Tables
I've two tables storing the scores of test marks. The following show the selected scores from the two tables of a certain person with id=1 and level=5:

# table1
id level score
1 5 80
1 5 80
1 5 100


# table2
id level score
1 5 40
1 5 60
1 5 50
1 5 70


The sum from table1 is 260 and the sum from table2 is 220 for a total of 7 tests. The grand total is 480.

I would like to be able to obtain the average of 68.57 (480/7) for a particular person of a particular level. Would I be able to do that with a single SQL? I've tried a number of sql queries but the don't work.

---

Running Average
How can I get mysql to do a running average of a given field using dates
from another column so that the average for each element is for a given time
period rather than from the 5 rows next to the element?

---

How Many Is Too Many Queries On The Average Web Page?
I'm just curious... I'm trying to watch how many queries I run in any given page. But I find myself being too conservative sometimes, trying to think of better ways to do things... but then end up getting frustrated.

Just looking for some average feedback. In your opinion or experience, how many queries to the DB using PHP is too many queries? I know less is better... i'm just trying to get an idea.

---

Query Fetching Average
I have 2 tables in a database. One table consists of persons. Each person appears only once in the table.....

---

Average Of Grouped Fields?
SYSTEM MOUNT KB_USED KB_TOTAL

host_1 / 1000 30000
host_1 / 1500 30000
host_1 /apps 1092 234343
host_1 /apps 7266 234343
host_2 / 4598 553444
host_2 / 4545 553444
host_2 /opt 3435 435345
host_2 /opt 4534 435345

What i want is, something like this

SYSTEM MOUNT AVG(KB_USED/KB_TOTAL)*100

host_1 / 4.166
host_1 /apps 1.78
host_2 / 0.82
host_2 /opt 0.91

How do I do this? Basically I want them grouped by mount per system.

---

Average Over Results From Query
I would like to write a query in which i calculate the average over the result from an earlier query. I have a transaction header table and a separate transaction lines table. I am trying to count an amount per transaction by summing the lines table, grouped by transactionnumber and would like to calculate the average over all of these sums.

---

Average Time Query
I have come stuck with averaging a column with type of Time.

I know there is a function AVG() that I can use to filter the average of a column, however, this brings back an integer.

Is there any way to return the average in time format (IE 00:00:00)?

BACKGROUND INFO:

SELECT `estimatedTime`
FROM job_sheet;

Returns,

01:00:00
21:30:00
01:00:00

The data type is time, and the default is 00:00:00.

---

High Average Load (50%)
I have a Windows 2003 server with MySQL 5 installed (5.0.19). The server has a 3ghz processor and 2gb of memory.

The server has about 20 databases of the type MyISAM and there is almost no load trough queries. The mysqld-nt.exe process has an average load of 50% and goes up when a query comes in. Why is this? I have tried logging to see what MySQL is doing but I cannot find anything abnormal.

---

Average Time Difference
I have a table which includes two timestamps - creationTS and processedTS.

I want to run a query that tells me the average difference between these two timestamps on a minute by minute basis, i.e. one row for each minute of the day, containing the total messages created in that minute and average amount of time it took to process data created in that minute.

Is this possible in a query, or do I need to grab each entry and use PHP to work out the average processing time?

---

Average Number Of Records
Say I have 10,000 records, each with a UNIX_TIMESTAMP()'d date field, and want to know the average on how many records have been created each a 24 hour period for the past 3 months. How would I go about doing that?

I tried the following but receive a group by error:
SELECT AVG(COUNT(*)) FROM my_table WHERE date >= TIMESTAMPADD(MONTH,-3,FROM_UNIXTIME(UNIX_TIMESTAMP())) GROUP BY id

---

Result With The Highest Average
I need to return the result with the highest average which should be calculated by dividing total_value by total_num_votes.

How can I do the division in the query to get the result with the highest average: ( total_value / total_num_votes )

I'm guessing it should look something like this:

SELECT ( total_value / total_num_votes ) as average FROM ratings ORDER BY average LIMIT 1

---

Average Query Execute Time???
I have a query taking about 4 sec that gets only 18 records.. That's not good. What can I do to speen up my Query?
<CFQUERY DATASOURCE="#datasource#" NAME="qry_job_search">
SELECT DISTINCT (t1.JobPostId), t1.Actionlkp, t1.Date,
tbl_job_posting.*,
tbl_job_post_requirements.*,
tbl_employer_info.*,
tbl_employer_url.*,
tblkp_employment_type.*,
tblkp_education_exp_lvl.*,
tblkp_salary_range.*,
tblkp_employment_exp_lvl.*,
tbl_job_post_location.*,
tbl_job_post_job_categories.JobCat,
tblkp_countries.*,
tblkp_prefectures.*
FROM tbl_job_post_history AS t1
JOIN tbl_job_post_location
ON tbl_job_post_location.JobPostID = t1.JobPostID
JOIN tblkp_countries
ON tbl_job_post_location.CC1 = tblkp_countries.CC1
JOIN tblkp_prefectures
ON tbl_job_post_location.CC1 = tblkp_prefectures.CC1
AND tbl_job_post_location.PrefectureID = tblkp_prefectures.ADM1
LEFT JOIN tbl_job_post_requirements
ON tbl_job_post_requirements.JobPostID = t1.JobPostID
LEFT OUTER JOIN tbl_job_post_job_categories
ON tbl_job_post_job_categories.JobPostID = t1.JobPostID
JOIN tbl_job_posting
ON tbl_job_posting.JobPostID = t1.JobPostID
JOIN tbl_employer_info
ON tbl_employer_info.UserID = tbl_job_posting.UserID
LEFT JOIN tbl_employer_url
ON tbl_employer_url.EmployerID = tbl_employer_info.EmployerID
JOIN tblkp_employment_type
ON tblkp_employment_type.ID = tbl_job_posting.EmploymentType
JOIN tblkp_education_exp_lvl
ON tblkp_education_exp_lvl.ID = tbl_job_posting.EducationExpType
JOIN tblkp_salary_range
ON tblkp_salary_range.ID = tbl_job_posting.Salarylkp
JOIN tblkp_employment_exp_lvl
ON tblkp_employment_exp_lvl.ID = tbl_job_posting.EmploymentExpType
WHERE t1.JobPostId NOT
IN (
SELECT DISTINCT (JobPostId)
FROM tbl_job_post_history
WHERE Actionlkp =4
AND date = (
SELECT MAX( date )
FROM tbl_job_post_history
WHERE JobPostId = t1.JobPostId )
AND JobPostID = t1.JobPostID
)
AND HistID = (
SELECT MAX( HistID )
FROM tbl_job_post_history
WHERE JobPostID = t1.JobPostId
AND Actionlkp =1
)
AND Date >= ( curdate( ) - INTERVAL 30 DAY )

<CFIF SESSION.COUNTRY_A NEQ "" AND SESSION.PREFECTURE_A EQ 0 AND SESSION.CITY_A EQ "">
AND tbl_job_post_location.CC1 = '#SESSION.COUNTRY_A#'
<CFELSEIF (SESSION.COUNTRY_A NEQ "" AND SESSION.PREFECTURE_A NEQ 0 AND SESSION.CITY_A EQ "") OR FORM.PREFECTURE NEQ 0>
AND tbl_job_post_location.CC1 = '#SESSION.COUNTRY_A#'
AND tbl_job_post_location.PrefectureID = '#SESSION.PREFECTURE_A#'
<CFELSEIF SESSION.COUNTRY_A NEQ "" AND SESSION.PREFECTURE_A NEQ 0 AND SESSION.CITY_A NEQ "">
AND tbl_job_post_location.CC1 = '#SESSION.COUNTRY_A#'
AND tbl_job_post_location.PrefectureID = '#SESSION.PREFECTURE_A#'
AND tbl_job_post_location.PostCity LIKE '%#SESSION.CITY_A#%'
</CFIF>
<CFIF (SESSION.CATEGORY_A NEQ 1) OR FORM.CATEGORY NEQ "">
AND tbl_job_post_job_categories.JobCat = '#SESSION.CATEGORY_A#'
</CFIF>
<CFIF (SESSION.KEYWORD NEQ "") OR FORM.KEYWORD NEQ "">
AND t1.Actionlkp = 1
AND (tbl_job_posting.JobTitle
LIKE '%#session.Keyword#%'
OR tbl_job_posting.JobDescription
LIKE '%#session.Keyword#%'
OR tbl_job_post_requirements.JobRequirements
LIKE '%#session.Keyword#%')
<CFIF SESSION.KEYWORD NEQ "" AND SESSION.PGV NEQ "brief">
<CFSET SESSION.PGV = "detailed">
</CFIF>
</CFIF>
<CFIF SESSION.EMP_TYPE_A NEQ 1>
AND tbl_job_posting.EmploymentType = '#SESSION.EMP_TYPE_A#'
</CFIF>
<CFIF SESSION.EXP_LVL_A NEQ 1>
AND tbl_job_posting.EmploymentExpType = '#SESSION.EXP_LVL_A#'
</CFIF>
<CFIF session.SRT EQ "D">
ORDER BY Date DESC
<CFELSEIF session.SRT EQ "C">
ORDER BY tbl_employer_info.CompanyName
<CFELSEIF session.SRT EQ "L">
<CFIF form.country NEQ 0>
<CFIF form.prefecture NEQ 0>
<CFIF form.city NEQ 0>
ORDER BY tbl_job_post_location.PostCity
</CFIF>
<CFELSE>
ORDER BY tblkp_prefectures.FullNameS, tbl_job_post_location.PostCity
</CFIF>
<CFELSE>
ORDER BY tblkp_countries.CC1, tblkp_prefectures.FullNameS, tbl_job_post_location.PostCity
</CFIF>
<CFELSEIF session.SRT EQ "T">
ORDER BY tbl_job_posting.JobTitle
</CFIF>
</CFQUERY>
Can anybody see a shortcut to get the same results??

---

Average Query Results Not Correct
This query works, but average results are not correct.

I don't no what the problem is!

Should i use subquery's?

Joining is not working fot this type of query's?

---

Average Query With 2 Rows From Same Table
My table:
"answer"
answerID
answer(int)
questionID(int)
userID(int)

answer1 is questionID = 1
answer2 is questionID = 2
WHERE userID is the same for both answer1 and answer2

I want the average of answer1/answer2: AVG(ans1/ans2), but how?

---

Find Average Amt Of Days Dates
I've been working on this problem for quite some time now. I've searched google and all over these forums, but didn't quite find what I'm looking for.

Here's what I'm doing:

mysql> SELECT idno, sro, pname, adate, cdate , TO_DAYS(cdate)-To_DAYS(adate) as days FROM turnaround group by idno,sro order by adate;
+------+----------+--------------+------------+------------+------+
| idno | sro | pname | adate | cdate | days |
+------+----------+--------------+------------+------------+------+
| 4 | S1111115 | Tupperware | 2003-05-03 | 2003-05-05 | 2 |
| 5 | S1111116 | Tupperware | 2003-06-03 | 2003-06-05 | 2 |
| 3 | S1111114 | Blab | 2004-05-03 | 2004-05-05 | 2 |
| 2 | S1111112 | Product Test | 2005-03-11 | 2005-03-11 | 0 |
| 1 | S1111113 | Big Tester | 2005-04-06 | 2005-04-08 | 2 |
| 6 | S1111111 | blah blah | 2005-11-18 | 2005-11-22 | 4 |
+------+----------+--------------+------------+------------+------+
6 rows in set (0.00 sec)

What I need to do now is find the average number of days. I've tried several combos of AVG() without sucess. Could someone point me in the right direction?

---

MySQL Order By Average Of Two Fields
I have made a Toplist mod for vBulletin, and it currently has a setting to choose to rank the sites by their in hits, or their out hits. I would like to add another setting that can rank them by the average of the in and out hits for each of the sites in the Toplist. Does anyone know how I can have MySQL order them by the average of those two fields?

---

MySQL Round And Average Functions
I have a column in a mySQL database table that stores a rating from 1 to 5.
I wish to select all rows from this table and get the average rating rounded to the nearest whole number.
I imagine its something like this

"SELECT ROUND(AVG(rating)) from ScoreTable"

---

Column That Calculates Average Based On Other Columns?
is it possible to have a column called "average" that adds up and then averages out the data in other columns?

I've got a database with several columns of scores. I'd like to have a column that holds the average score. Then on my website, my visitors can sort by the highest score and I'll be able to pull the info straight from the db..

furthermore, only certain columns hold scores, they're named like "scoreA", "scoreB" and so on...

is this at all possible?

If not, can anyone point me to some php resources that will allow me to calculate hundreds, perhaps thousands of scores (on the fly) and present them from high to low (or low to high)?

---

Calculate A Moving Average And Updating A Table With It
I'm learning SQL and now I'm trying to update a table (koersen) with stockdata (date, ticker, open, close, volume, MA10vol).
The column MA10vol is empty and should contain the avg volume from the last ten records with the same ticker.
What I tried sofar:

SELECT @hdat:= (SELECT DISTINCT DATE_FORMAT(datum, '%Y-%m-%d') FROM koersen ORDER BY datum DESC LIMIT 0,1);
SELECT @ldat:= (SELECT DISTINCT DATE_FORMAT(datum, '%Y-%m-%d') FROM koersen ORDER BY datum DESC LIMIT 0,10);
SELECT tickerVL, AVG(totvolume) AS MA10
FROM(
    SELECT  SUM(k.volume) AS totvolume
        FROM koersen k WHERE k.datum BETWEEN @hdat AND @ldat
        GROUP BY k.tickerVL
    ) AS tijdelijk

The update part comes later. I'm now trying to make a subquery to get me the total volume of the last ten days. But I get a message that the subquery returns more then 1 row (more tickers I think).

But I want a query that updates every MA10vol for every ticker. I know I'm doiing something wrong but what?

---

Int Length?
So I'm working on a new table for a game database which keeps track of the player's inventory. As you can imagine there are a lot of items in the game, and thus a lot of primary keys are needed. Originally I had the primary key as an int(11). I thought increasing it to int(20) would give me more primary keys to work with, but I didn't see a different. I'm now using bigint, but I'm still stumped about the significance with a field's length. So... what's the deal?

---

Length Of Data
if I make a column INT(255), and I know 100% sure that no data will be no more than 5 numbers long, am I *using* any extra CPU resources, or is it exactly the same?

---

Length Of Blob
How can I find the size of data in KB in a blob field?

---

Get Field Length
I have a lot of varchar fields of various lengths in my database. I want to implelemnt some simple HTML validation for my PHP pages, using the "maxlength" attribute of input statemaents to limit the max length to the length of the fields.

Are there any MySQL or PHP funcitons that will give me the length for a given field? I've looked around and so far i think the only way to do this would be to use the desc statement in MySQL and then use substring functions in PHP to get rid of the varchar() bit and be left with just the number. Was wonderring if there is a better way. Oh my database type is innoDB or whatever the default is. I'm running MySQL 4.0.20-standard and PHP 4.3.7

---

Max Length Of Varchar
I'm setting up a news page on my site and I am wanting to use MySQL to store all of the news entries, but the max of the varchar() value I am using for the column is not large enought for my needs... any suggestions?

---

Max Key Length Error
I'm trying to set up a database table, and I keep getting an error saying:

Max key length is 500

I have looked everywhere I can think of but I can't find an explanation
for what is causing the problem so I can fix it,

---

Max Field Length
How do i retrieve the actual maximum length of a field?

mysql_fetch_field -> max_length returns the largest data stored in the field.
not quite what im after.

---

Recordset Length In PHP
I have the following peice of code in a PHP page:

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$categoryName = $row[1];
$categoryDescription = $row[2];
$pos = count($aryCategories);
$aryCategories[$pos][0] = $row[3];
$aryCategories[$pos][1] = $row[4];
$aryCategories[$pos][2] = $row[5];
$aryCategories[$pos][3] = $row[6];
}
if (count($aryCategories) == 0) {
header('location:directory.php');
}

Is there a way I can test the length of the recordset to trigger the redirect, rather than building the array (or not) and testing the length of that. Something like:

if (rs.length < 1) {
//redirect
} else {
//while loop
}

Also, while you're looking, is there a way I can set the $categoryName and $categoryDescription variables before the while loop? These values are the same for every row returned in the recordset so I don't need to reset them to the same value every time in the while loop. Something like:

if (rs.length < 1) {
//redirect
} else {
$categoryName = firstRowFirstField;
$categoryDescription = firstRowSecondField;
//while loop
}

---

Column Name Length
how long can table column names be ?

---

COntrolling The Length
I'm currently trying to do a query on a mysql database which is going to be put into a drop down box on my website. However, some of the fields in question are very long (200+ characters) and this means that the drop boxes stretch right across the screen when displayed.

I was wondering if there is anyway in mysql to truncate the results I get. I've looked on google and everything I find refers to the LIMIT keyword, which is not what I am looking for.

---

Length With Varchar
varchar uses only the require space, so, what's the benefit of defining a length to the field when the type is varchar

---

Length Of A Column
I want to view only the names from a table, which are less in length, that 10 characters

---

Character Set & Length
The default encoding of MySQL is latin1. So, textual data should be
interpreted as 8-bit character. Is that right?
If the default character set to , say big5, in server startup, will
MySQL interpretes all textual data as 16-bit character?
e.g. in latin1 encoding the word "hello" is stored as : hello (5
characters + null terminator)
how will it be interpreted if the default encoding is changed to big5?
Will each 8-bit character be extended to 16-bit representation? Or will
2 8-bit characters combine to form 1 16-bit character?

---

MySQL 4.1 Max Key Length In Utf8
I am testing on mySQL 4.1.2. I just converted some of my varchar(255) fields from character set ascii to utf8. I noticed that the primary key defined on these fields got redefined to only indexing on the first 84 characters. I can understand why it shortens it by 1/3 since max length in MySQL for utf8 character is 3 bytes. However, why does it need to shorten it at all when the max key length for innodb is 1024?

Also, is there a way to increase the max key length on both innodb (1024 bytes) and myisam (500 bytes)? Our database requires creating indexes on multiple varchar columns. With that max key values on utf8, it really restricts our use in multi-column indexes.

Btw, why is max length on utf8 3 bytes in MySQL? Shouldn’t it be 4?

---

Index Exceeding Max Length?
Does anyone know why the following code generates the following error?

CREATE TABLE test1
(
test text(256) NOT NULL,
PRIMARY KEY (test(256))
);

CREATE TABLE test2
(
id int NOT NULL,
test1 text(256) NOT NULL,
test2 text(256) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (test1(256)) REFERENCES test1 (test)
);

CREATE UNIQUE INDEX test4_index ON test5
(
test1(256),test2(256)
);

#1071 - Specified key was too long; max key length is 1000 bytes

I thought 256+256=512

---

Infinite Length Field
How do I make a field in mySQL that can have unlimited characters of any kind?

---

Maximum Length Of INSERT
If you use code in the style ofCODEINSERT INTO 'tablename' ('TOWN','COUNTY','POSTCODE') VALUES
    ('Arundel',West Sussex','AR'),
    ('Stroud','Glos','GL'),
    ...
    ...
    ('Wells','Somerset','WE');

---

MySQL LENGTH Parameter
lets say we have a table named TEST with just one column called NUMBER.
The datatype for NUMBER is set as SMALLINT(3). "(3)" is the LENGTH parameter.
At first glance I would think that its purpose is to limit the number of digits allowed in the column called NUMBER. Maybe this really is its purpose and I'm just not understanding.

According to http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html :

" Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width is used to display integer values having a width less than the width specified for the column by left-padding them with spaces.

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters. " Code:

---

The Actual Length Of A Column
I developing a system using mysql 5.0, which have to handle unicode in few columns. What I read from the manual is if I set the column charset to utf8, the actual length for, let say varchar(100) will be divided by 3, since 1 character of utf8 type use up 3 bytes.

If I set the database and the table charset to utf8, is that all the columns will be affected, which means their charset are also utf8, or just the 1 specified will?

---

Change Autonumber Length
I have a table and with an increment ID which id now SN001 but i would like to add an extra 0 to make it SN0001...any idea how to do this??

I tried update like this
UPDATE CUSTOMERS
SET CUSTOMER_ID = "SN0+[CUSTOMER_ID,4]"

---

Mysql Varchar Length
what happen if we try to enter characters or numeric value more than the specified for varchar(11) or int(11) field?e.g.When we try to enter more than 11 chars or numberic values.I m a newbie in mysql.I wud b very much pleased.

---

N-length String Fields
I've managed to make a few member-driven websites with fleeting knowledge of the syntax. Now I'm looking forward, and how to do it better. Mostly, I want to know how to have a field that stores a string of n characters, normal C NULL terminated string. So far I've been doing VARCHAR(45), which to my understanding is 45 characters long. Does a field of type VARCHAR allow for what I want (up to a logical limit)?

---

Get Length Of Column Names
is there a possibility to check if somewhere in a table/db a column exists whichs name is longer than, for example, 50 chars?

---

How To Modifiy Max Keyfile Length
I have reached the limit Max keyfile length on one table :

MyISAM file: IOCADM_syslog/records_dhcp
Record format: Fixed length
File-version: 1
Creation time: 2007-01-27 0:36:54
Recover time: 2007-08-24 4:01:22
Status: open,changed
Auto increment key: 1 Last value: 157613405
Data records: 123164257 Deleted blocks: 10108991
Datafile parts: 133273248 Deleted data: 1142315983
Datafile pointer (bytes): 4 Keyfile pointer (bytes): 3
Datafile length: 15059877024 Keyfile length: 17179769856
Max datafile length: 485331304446 Max keyfile length: 17179868159
Recordlength: 113

How can I modify this limit?

---