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
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Number Of Records Per-Month
i have got a script that log all downloads (daily) in a database. I would like to know how to query that database, to get the number of records per month. My records look like this date ip 2006-01-01 200.1.157.11 2006-01-01 192.168.10.3 2006-02-05 198.10.10.2 etc needed output 01 2 02 1
View Replies !
View Related
LIMIT :: Total Number Of Records?
If I have a sql statement like this: Code: SELECT id FROM `asset` WHERE field = 'value' LIMIT 20 , 40 Is it possible to know how many results there would've been if I hadn't used the LIMIT? Or will I need to do the above query and just count the results first, then apply the LIMIT and run the query again to get my limited set of results?
View Replies !
View Related
Limit :: Limited Number Of Records
I'm running a query to get a list of students and the class they belong to. Is there any way I can run a single query to return only 4 students from every class if there are more than 4 in some of them? The students have student ids, so I would want the first 4 student ids.
View Replies !
View Related
Find All Records With A Certain Number Of Characters
I have had a data problem where a field length has been truncated at 25 characters, rather than the 100 specified in the data type, varchar(100). I believe the problem to be on our website form input and not in MySQL (I didn't code the website, and I'm hoping the guy who did can correct the problem), but I would like to be able to find all the records that have been affected. Is there a WHERE statement that can find all records with at least 25 characters in a particular field?
View Replies !
View Related
Limiting Number Of Records & Specifying Order
I have a table of results, including resultNumber and resultDate as fields. I want to retrieve the 25 most recent (by date) records ordered in ascending date order. So if for a particular user there are 50 results, I want to get the most recent 25 in ascending date order. I thought the answer was a subquery with a ORDER BY resultsDate DESC LIMIT 25 on the inner query and an ORDER BY resultsDate ASC on the outer query, but I get an error message saying not supported in MySQL 4.1.22 which my ISP has.
View Replies !
View Related
List The Number Of Records In Query
I have a very simple query. I want to list a bunch of names & how old they are. (two columns) I want to add a third column that basically counts how many records there are. Example: 1. Bill Jones - 55 2. Bob Smith - 56 3. Steve Gates - 25 Can I do this in mysql or should I add the code in PHP, and how do I do it?
View Replies !
View Related
Administrator Rows Vs Actual Number Of Records
I imported some table (about 1500 records) using "LOAD DATA LOCAL INFILE..." (command line console). No warnings, no skipped, no deletes - all the recored are written to be imported. However, in MySQL Administrator UI tool I see completely different number in the "Rows" column. Making a SELECT query shows that the actual number of records in the table is the correct one (the same number as in text file table). Command line SHOW TABLE STATUS FROM ... doesn't show any of these numbers. I would expect that the "Rows" column doesn't show the actual number of recored. But, the information about of 100 enteries table is shown correctly (rows=records=100).
View Replies !
View Related
Join Tables To Count Number Of Records?
I've two tables: responses ========== response_id schema_id timestamp answers ========== answer_id response_id answer (that's a cut down version, but will do for this question) I need to get all the responses where there is at least one answer in the answers table. But I do not want the answer data. It's literally a quick check for an export to say "get me all the responses where there's at least one question answered". I have this: Code:
View Replies !
View Related
Counting The Number Of Records That's Greater Than The Limit
I have a table that has thousands of schools in it. I'm returning all the schools in a certain state. I'm limiting it to 20 per page. However, I want to display text that says how many there actually are...exampe: 'showing 1-20 of 43'. Is this possible? My query is below: SELECT id, city, CONCAT(school_fname, school_lname) AS thename FROM ccn_schools WHERE state='KS' ORDER BY CONCAT(school_fname, school_lname) LIMIT 20 So how do I have to run this query without a limit to get that number?
View Replies !
View Related
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.
View Replies !
View Related
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?
View Replies !
View Related
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?
View Replies !
View Related
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?
View Replies !
View Related
Average Between Two Dates
I am trying to figure out how to find the avg in between two dates in the work_date field. For example, let's say I want the avg from 2007-01-24 to 2007-05-06, I would have to find the avg by taking the values in the daily_typing_pages columns and add up all the columns between the the dates provided. (E.G. (100+220+350+250+170)/5).
View Replies !
View Related
Calculating 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.
View Replies !
View Related
Getting Average Of The Count
My table (site_visit) is as follows DATE | SITE 01-01-06 | London 01-12-06 | New York 01-30-06 | Chicago 02-02-06 | Mumbai 02-05-06 | JBerg 02-20-06 | Munich 03-05-06 | New York 03-10-06 | JBerg I am trying to get the average site visits per month so far I can get the count per month using: SELECT count( * ) FROM `site_visit` GROUP BY Date_Format( date, '%m %Y' ) How can I get an average of count(*)? Iam using MySQL 4.0.14 I can use php to do this, but I am trying to minimize the use of PHP.
View Replies !
View Related
Average Length
Basiically I have a table called ‘Members’ with the following attributes, (the m is short for member), Members (mno , mname, date_joined, date_left, maddress, mtel) I need to produce a single SQL statement that will output the longest length of current membership, the shortest length of current membership and the average length of current membership.And the out put I get should be easily understood, not just a number if you what i mean.
View Replies !
View Related
Calculate Best 3 Average
I am looking for help to calculate an average based on a top 3 score. Potentially there could be up to 10 entries per user & I need to calculate what their best 3 scores are as an average. you also need to bear in mind that there could be duplicate scores, which would need to be excluded. For example user 1 could have entries as below 30, 20, 43, 30, 45, 23, 27, 30, 41, 20 which would give you the best 3 scores average as 38.7. Can anybody out there help me achieve this in a query?
View Replies !
View Related
Average From Two Tables ...
I have one table called 'ratings_student' as below: def_user_id | rating_student ________________________________ ____61______|________2_______ ____61______|________3_______ ____62______|________5_______ ____62______|________4_______ ____63______|________2_______ ____63______|________5_______ and the other called 'ratings_teacher' as below: def_user_id | rating_teacher ________________________________ ____61______|________4_______ ____61______|________5_______ ____62______|________1_______ ____62______|________2_______ ____63______|________3_______ ____63______|________5_______ If you could help I need to work out the sql query to get the average rating from BOTH TABLES COMBINED and GROUP BY def_user_id I have a good idea of what's required as I am able to do it for individual tables but not both table ratings combined...
View Replies !
View Related
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);
View Replies !
View Related
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.
View Replies !
View Related
Ordering Average
I'm wondering how should I sort my results. I have the sum of the votes and the number of voters. I'd like to sort my results in a way that the highest average score is the first and lowest is the last. The problem is that if I have one voter that voted 10 on something the average will be 10 and by regular ordering will be first but that wont represent the true results as more voters is not taking any effect here when it should.
View Replies !
View Related
Count On 2 Columns And Average
if I have table with columns which include the following two and some sample data: name type fred abc fred abc john def eddy abc fred xyz john def bill abc 3 3 Is it possible to obtain - a total of the distinct names that have a particular type (say abc which is 3 - fred, eddy, bill) and the total of the same type (abc which is 4) - get the average which is tot_type (4) / tot_name (3)
View Replies !
View Related
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
View Replies !
View Related
Union Of Tables / Average
I have one table called 'ratings_student' as below: def_user_id | rating ________________________________ ____61______|________2_______ ____61______|________3_______ ____62______|________5_______ ____62______|________4_______ ____63______|________2_______ ____63______|________5_______ and the other called 'ratings_teacher' as below: def_user_id | rating ________________________________ ____61______|________4_______ ____61______|________5_______ ____62______|________1_______ ____62______|________2_______ ____63______|________3_______ ____63______|________5_______ If you could help I need to work out the sql query to get the average rating from BOTH TABLES COMBINED and GROUP BY def_user_id I have a good idea of what's required as I am able to do it for individual tables but not both table ratings combined...
View Replies !
View Related
Selecting Average Per Month
********************** *PDATE **** Price * ********************** *2008-12-03** 22 * ********************** *2008-12-01** 36 * ********************** *2008-12-09** 54 * ********************** *2008-11-05** 21 * ********************** *2008-11-15** 22 * ********************** ******************** *Month **** AVG * ******************** *December ** 22 * ******************** *November ** 36 * ********************
View Replies !
View Related
Date_format :: Average By Week
I have used the following query to try and average by week select date_format(calldate, '%Y-%m-%d 23:59:59'), avg(billsec) from Clovercdr where calldate Between '2006-02-01 00:00:01' and '2006-03-22 23:59:59' and branchname = 'Boksburg' and billsec > 0 and Network = 'SAMobile' GROUP BY date_format(calldate, '%x%v') I know that it should be averageing every week on monday: 1. is there any way I can average every week from the first day ie. first day is 2006-02-01 which is a wendsday, every wendsday for the given time period 2. I dont understand my results here ....
View Replies !
View Related
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.
View Replies !
View Related
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?
View Replies !
View Related
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.
View Replies !
View Related
Average, ROUND, COUNT
I'm trying to build my first dynamic web page using PHP & MySQL. I have a fuel economy page that I'm trying to improve by using a DB. I've cobbled the following code together and I'm having partial success but still learning how to use PHP & MySQL. The code below is on this page (www dot njdot dot us/buell/ulydata2.php) and I plan on using a form to add data. Sorry about the link but it would not let me post a real link Part of the trouble I'm having is that the arithmetic is not correct and I'm not familiar enough with PHP/SQL to know how to change it to get the query I'm looking for. What I'm trying to place in the table is the average miles per gallon for each user along with model year and the number of samples in the DB for that user. If someone has the time to review this and offer some suggestions that would be great! PHP Code: ....
View Replies !
View Related
Average Of Top Results For Each Country
Tables are; users: ------ uid country (two letter acronym) teams: ------- uid score Average score for each country (top 150 countries, more than 50 users per country minimum): Code: SELECT COUNT(u.country) AS players , u.country , AVG(t.score) AS avg_score FROM users AS u LEFT OUTER JOIN teams AS t ON u.uid = t.uid GROUP BY u.country HAVING players > 50 ORDER BY avg_score DESC LIMIT 150
View Replies !
View Related
How To Add Count Number Of Words With Showing Limited Number
SELECT SUBSTRING_INDEX(classDescription,' ',13) as classDescription, listing.inv_company, listing.inv_addr2, listing.inv_state, listing.classcatid, listing.industryid, listing.listingID, listing.inv_country, listing.adscreation_date
View Replies !
View Related
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?
View Replies !
View Related
|
TRACKER
