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?
View Complete Forum Thread with Replies
Related Forum Messages:
Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP $gettray = mysql_query("SELECT trailers.title, trailers.link, trailers.movie, movie.title AS mtitle FROM trailers,movie WHERE trailers.movie=movie.word ORDER BY trailerid DESC LIMIT 6",$connm); It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table. I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet. If the row does not exist in 'movies', the program than uses the entire trailer title like so PHP if($ttray['mtitle']) { $newttitle = explode("-",$ttray['title']); $newttitle = array_reverse($newttitle); $ttitle = $newttitle[0]; $ttitle = $ttray['mtitle'] ."- ". $ttitle; } else { $ttitle = $ttray['title']; } Thanks Ryan
View Replies !
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??
View Replies !
Help With 3 Table Query & Counting Rows In Third Table
I have three tables, a members, a vendors and a products. I'm using a query to grab all of the rows from the first two tables, matching on a primary key. This is easy and works fine. However, I'm trying to pull the number of products from the third table and it's giving me trouble. Basically, I can count the products for each vendor if products exist, but if there are no products, instead of returning 0, no rows are returning. current query looks like this: SELECT t1.*, t2.*, count(*) AS count FROM vendors as t1, members AS t2, products AS t3 WHERE t2.mem_id = t1.mem_id AND t3.vendors_id = t1.vendors_id GROUP BY t1.vendors_id; So, all of the vendors that have zero products are being left out of the results. Does anyone know if it's possible to achieve this without using a temp table?
View Replies !
Query Delete All Rows From The Table?
I have a table called "repairs" with multiple rows (let's say for the purposes of this post - from repair_id=1 through to repair_id=10) Why does this query delete all rows from the table? DELETE FROM repairs WHERE repair_id=3 OR 4
View Replies !
Select Double Rows In One Table In One Query
I try to select double rows in the same table within one query. I can do it with 2 query's but i wonder if it's possible with one? The table 'admin_category: Quote: id name sub 1 cd 0 2 dvd 0 3 action 1 4 new 1 5 general 2 The current query which i use: PHP Code:  SELECT a.name AS sub, b.name AS category FROM admin_category a LEFT JOIN admin_category b ON a.sub = b.id WHERE a.id = '4' What i try to reach with above query is that row 4 will be selected, take the name of it (new) and also the main category (b.name as category) by doing a left join on the same table where the sub id = the id. The output of above query is: sub = new category = NULL (Category should be 'cd' but it's not)
View Replies !
How Do I Make Rows From Another Table Appear As Columns In Query?
I have a join query (which r937 kindly helped me out with) which joins a table of tracks with a table of albums in a many-to-many relationship. I also have a table called links with a column of URLs called links. There's also a column of link categories called linkTypes which is of type enum with three possibilities - artist, album and track. What I'd really like to do is to add three columns to the result set (if that's the correct term) of the join from above i.e. I'd like to add trackLinks, artistLinks, albumLinks. So I guess my question is: How can I make columns on the fly by using field values from another table? In answer to the first thquestion - why don't you just add the columns to those two tables? The answer is because in the future I will need an undetermined number of each of these columns, e.g. one artistLink column per affiliate - artistLinkItunesUS, artistLinkItunesUK etc. So is this something I can feasibly do without restructuring the database or should I use a couple of queries and try combine the data through PHP? Or perhaps I'm looking at the relationships incorrectly? Having to add columns like that does seem like a one-to-many relationship which I haven't taken care of.
View Replies !
Count One Table's Rows From Multi Table Query
here are my tables (condensed) FEEDS feed_id site_id SITE site_id site_name ARTICLES article_id feed_id link I want to create a query that returns the total number of articles for every site_id (which is unique in the SITE table). I have this: PHP $gsite = mysql_query("SELECT site.site_id, feeds.feed_id, COUNT(articles.article_id) AS acont FROM site,feeds,articles WHERE feeds.site_id = site.site_id AND articles.feed_id = feeds.feed_id group by site.site_id", $connection) or die(mysql_error()); The query does not use JOIN, ON and all that good stuff. I just need the following variables to run through a loop: site_id the number of articles rows per site_id
View Replies !
Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc). When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query. I tried it under phpmyadmin, and once again I get zero rows. Why do I get inconsistent results? Here's the query:
View Replies !
Match Rows In Table B With Rows In Table A
How do I structure a query to match rows in table B with rows in table A where column in B contains strings that contain data from column in A.. Example: B.part = "abcdefg" matches A.part ="cde" I cannot put literal in query.. it must be from column data.. It's easy to match rows where columns are equal, but I can't figure out how to get a match with "substring" as shown.
View Replies !
Getting Rows That Are Related To Other Rows In The Same Table
I use a table to save a map using the following structure: id, x, y, owner Every occupied map filed has an owner id != 0. The owner id is = 0 for vacant fields. now the problem: New registered users need a vacant field on the map. Moreover the mapfields around this field need to be vacant as well! (sqrt((t1.x-t2.x)*(t1.x-t2.x)+(t1.y-t2.y)*(t1.y-t2.y)) <= 5.25) What I need is a query that gets those fields that have vacant fields around them. So far, all my tries to solve this problem with Joins/Suvqueries failed.
View Replies !
Delete Rows From Table A, Which Dont Exist In Table B (base On Column X)
I have 2 tables identically structured. A & B Table A, has column: Product (product code) as primary key Table B doesn't. Apart from that they have the exact same fields. There's also a column: supplier I want to Delete * from table A, where does not exist in B (based on column: Product) & where supplier = apples So to elaborate. Table A is my main table, but it now contains outdated products from supplier apple. Table B has the latest list of products from supplier apple. So I want to remove old products from A that supplier apple no longer makes. mysql version 4.0.27
View Replies !
How To Delete From 1 Table Some Rows Based On Match Results In 2nd Table?
How to delete from TABLE-1 all rows with indexes "i" that match to index j=2 from TABLE-2? TABLE 1: +---+------+ | i | name | +---+------+ | 1 | item1 | | 1 | item2 | | 7 | item3 | <-- delete all rows with i=5,6,7 | 6 | item4 | <-- delete | 5 | item5 | <-- delete | 5 | item6 | <-- delete | 7 | item7 | <-- delete +---+------+ TABLE 2: +---+---+------+ | j | i | name | +---+---+------+ | 1 | 1 | item1 | | 1 | 3 | item2 | | 1 | 2 | item3 | | 2 | 5 | item4 | <---- j=2 => i=5 | 2 | 6 | item5 | <---- j=2 => i=6 | 2 | 7 | item6 | <---- j=2 => i=7 | 3 | 8 | item7 | +---+---+------+
View Replies !
Updating Rows In Table B Based On Related Field In Table A
Ver 4.1.8-standard for apple-darwin7.6.0 on powerpc (Official MySQL-standard binary) I am trying to do some data migration based. I have several tables that contain our legacy pkey field and I want to update the tables with new ID's. I need to do this several times and have tried it several ways to no avail. Table A --------- companyID int(10) pKey legacyID int(10) old legacy pkey Table B --------- bAID int(10) pkey companyID int(10) legacyID int(10) Table A has values for both companyID (unique key) and legacyID. Table B has values for bAID (unique key) and legacyID but companyID is empty. I need to update tableB.companyID with tableA.companyID based on tableb.cSerialID to tablea.cSerialID relationship. I need a query that will update ALL rows.
View Replies !
ALTER TABLE Deleted The Rows - Error: Table Is Full
Today when I tried to insert data in a table i received the error that 'table is full'. On SHOW TABLE STATUS, I noticed the size of table is grown to 4GB (rows 5359211). mysql> SHOW TABLE STATUS LIKE 'messages' G *************************** 1. row *************************** Name: messages Engine: MyISAM Version: 9 Row_format: Dynamic Rows: 5359211 Avg_row_length: 801 Data_length: 4294967288 Max_data_length: 4294967295 Index_length: 45783040 Data_free: 0 Auto_increment: 5406252 Create_time: 2007-04-20 18:26:38 Update_time: 2007-08-22 09:55:22 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) Here is the table structure. CREATE TABLE `messages` ( `id` int(11) NOT NULL auto_increment, `subject` varchar(255) NOT NULL default '', `message` text NOT NULL, `attachment_path` varchar(255) default NULL, `new` tinyint(4) NOT NULL default Ɔ', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; On searching I found the this link. http://dev.mysql.com/doc/refman/4.1/en/full-table.html According to manual i executed the following query MySQL ALTER TABLE messages MAX_ROWS=20000000000; I skipped AVG_ROW_LENGTH from the query, I was confused what should be the value for this. After executing the query, when i check the table status it displayed totally different picture. Now there were only 170109 rows left. mysql> SHOW TABLE STATUS LIKE 'messages' G *************************** 1. row *************************** Name: messages Engine: MyISAM Version: 9 Row_format: Dynamic Rows: 170109 Avg_row_length: 649 Data_length: 110563036 Max_data_length: 281474976710655 Index_length: 1961984 Data_free: 0 Auto_increment: 5409214 Create_time: 2007-08-23 10:07:08 Update_time: 2007-08-23 13:41:57 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: max_rows=4294967295 Comment: 1 row in set (0.00 sec) What could be the reason of this rows deletion? I am on 32 bit system. MySQL version: 4.1.18-standard-log Operating System : CentOS 3.x Memory: 4 GB DDR
View Replies !
How To Delete Rows In A Table Where Url Shows Only 1 Time In Whole Table
I don't know a whole lot about mysql, i need to fix some things in my db and what i described in the title is the first thing id like to do. Now, I have a table which lists referring urls, clicks to them and the time. Everytime i get a hit from lets say url1.com it gets a new row with a time id (like 1218613809). I am not resetting this table since it has to track the incoming hits forever since the moment they start sending traffic. But now the table starts getting the hickups now and then, it has about 850.000 records in it and it cant handle it anymore. I need to do a 'repair table' action and then it's good again, but it happens more and more often lately. So first i want to delete the rows of the urls who have only sent me 1 click. These urls who sent me 1 click only have one row. When an url sent me 20 clicks, that url is in 20 rows in the table. So I want to delete the rows of the table with the url's that only show once in the entire table. How do i do this?
View Replies !
Inserting Rows From Backup Table To Current Table
I have two tables, with identical structures. One is a mysqlorary table loaded with the information from the current table a few weeks back - it is essentially a backup table. I now need to insert a selection of data from the backup table into the current table. If an item from the backup table exists in the current table, it is to be overwritten. I was wondering on the best way of doing this? Would it be better to loop through a php array of table rows, and delete from the current table then re-insert from the backup table? If so, how would i maintain the primary key (it's auto-incrementing)? Or should i write the rows to be transferred from the backup table to the current table to a file, then use load data in file? Are there any other ways of doing this?
View Replies !
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 !
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 !
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 !
Last N Rows Of The Query
SELECT expensive query ORDER BY field ASC; It generates somewhere around 2.9m rows. I want the last 10: SELECT expensive query ORDER BY field DESC LIMIT 10 But I want them the other way around. Sure, I can do that programatically, but for "application reasons" I want that done in the query, so what I want to do is along the grounds of SELECT expensive query ORDER BY field LIMIT 10 OFFSET rows()-10 We're using MySQL 4.0. Is there a way to achieve the above without using a temporary table?
View Replies !
Several Rows From One Query
I've this sql-query... $sql = "INSERT INTO database (some_kind_of_id, names) VALUES $xxx, $_POST['xxx2']"; The thing is that my post xxx2 are several values that I want inserted on several rows with the values xxx in front...
View Replies !
How To Delete All Rows From One Table With Id NOT Found In Second Table?
How can I delete all rows from table `one` with id NOT found in table `two`? Here is my situation: Table `one` contains transaction on items identified by `id` Table `two contains detailed information on items, identified by the same `id`. Periodically, items are be deleted form Table `two`. My question is: How do I find (and delete) all the rows in table `one` that refer to an `id` that is NO LONGER in table `two` ?
View Replies !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
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 !
Eliminate Rows In A Query (without Many OR)
This is my query : SELECT cas.id,cas.noCas,cas.nomFictif,cas.prenom,cas.naissance FROM cas WHERE LEFT(noCas, 3)<'300' AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598 ORDER BY noCas There must be a more efficeint way to find what i want than this : AND cas.id<>53 AND cas.id<>61 AND cas.id<>173 AND cas.id<>174 AND cas.id<>178 AND cas.id<>185 AND cas.id<>598 Something like cas.id is not (list of values)...
View Replies !
Query Help, Comparing Rows
Suppose I have the following data: +----+----------+-----+-----+-----+-----+-----+-----+-----+ | Id | Time | Sun | Mon | Tue | Wed | Thu | Fri | Sat | +----+----------+-----+-----+-----+-----+-----+-----+-----+ | 11 | 11:20:00 | F | T | T | T | F | F | F | | 12 | 11:45:00 | F | T | T | T | F | F | F | | 14 | 12:10:00 | F | T | T | T | F | F | F | | 15 | 12:35:00 | F | T | T | T | T | T | F | | 17 | 13:00:00 | F | T | T | T | T | T | T | | 18 | 13:25:00 | F | T | T | T | T | T | T | | 19 | 13:50:00 | F | T | T | T | T | T | T | | 20 | 11:28:00 | F | T | T | T | T | T | F | | 21 | 11:53:00 | F | T | T | T | T | T | F | | 22 | 12:18:00 | F | T | T | T | T | T | F | +----+----------+-----+-----+-----+-----+-----+-----+-----+ I would like to output the data by day pattern. I need some way to determine that in the above table, Mon-Wed is the same, Thu-Fri is the same and Saturday and Sunday are unique.
View Replies !
How Many Rows Were Affected By Query?
How do I retrieve the number of affected rows by an UPDATE query with SQL? The C API exposes mysql_affected_rows() but I can't find documentation of the SQL equivalent... I'm trying to find if an UPDATE had an effect within a stored procedure, so I can do an INSERT if there's no row to update. Doing it the other direction to generate an error to act on would be quite wasteful... one INSERT and millions of errors per day.
View Replies !
SQL Query Not Outputing All Rows.
My query doesn't seem to output all 6 rows in the database only two queries and i'm unsure why. I do know it is the query as when i do SELECT * FROM case_studies It outputs all rows. The query i am usign is: select programs.program_title, programs.id, case_studies.id, case_studies.title, case_studies.author, case_studies.timestamp from case_studies, programs WHERE programs.id = case_studies.id Any ideas?
View Replies !
Query With 3.3million Rows Is Slow?
I'm not that great with MySQL...so I was hoping someone could help me out. The query I'm running is too slow...can anyone tell me what I can do to speed it up..if I can at all? I was wondering if because ZipListMatrix has 3.3 million rows that 8 seconds is all the faster it's going to be. Any help is greatly appreciated! I have already "optimized" the tables.
View Replies !
Get Rows From Multiple Tables In One Query
Is there a way to select rows from multiple tables in one query? Say I have the following tables and columns:Storestore_idItemitem_iditem_store_id I want to get a store by it's id + all the items associated with that store id. Do I have to make two separate queries for this? One to get the store, and another to get all the items for that store.
View Replies !
Query Browser :: How To View Rows?
I'm having a hard time with the Query Browser. I can't figure out how to view the rows. When I click on the table in the Browser, nothing shows up and at the bottom there is a message saying Library Error Number 3. I am sure this is something simple but it's making me tear my hair out.
View Replies !
Performace Question. 525,600 Rows In One Query
I am wondering which would be the best option. I have a database holding prices for every minute of trading. This means that getting a year of price data for graphing would mean that I am potentially requesting 525,600 (more on a leap year) rows. The graph only needs to have a resolution of one point per day, so should I: 1. query 525600 rows and then take one price per day 2. create a query to only take one price per day ?
View Replies !
Getting Rows Multiple By List Query
I have a list of values (constants) that I use to find matching rows in a single table as follows: "select id, name from articles where id in(1,2,3,2,3,2,2)" seems very simple, but my problem is, the query delivers only 3 lines, of course, because the last four numbers are redundant. But my need is, to get a row for each number in the list, wheather it is doubled or not.
View Replies !
|
TRACKER
