Totals/Mean Values
I assume this is a very simple question, I just don't know the answer!
I am planning on setting a MySQL database for some real estate property that has already been sold. The user will enter the information for each parcel sold for homes, land, etc.
What I want to do is:
a) Total the price columns for each and take the average price - have all of this calculate automatically
b) Also call on these total values from an intro page displaying the totals for each year
First, is this possible?
Second, how would I go about doing this? Do I need to add extra fields to the table for these totals, or are there MySQL commands to do the arithmetic
View Complete Forum Thread with Replies
Related Forum Messages:
Counting Totals
I have a table of messages. Each message has a me_date datetime. I want to get a count of the number of messages every day in the last 30 days - even the days when there were none. How can I do this in a single statement? so far I have: select count(me_id) as a, to_days(me_date) from messages where to_days(me_date)>to_days(now())-90 group by to_days(me_date) but this doesn't include the 'zero' days (days when there were zero messages).
View Replies !
Indexed Totals
don't know if that subject is correctly put, but here's what i'm trying to accomplish: I want to be able to tell how many rows in a given table, and for a given INDEXED column, carry any given ID. Example: suppose I have a field named 'IDNumeric' defined as decimal(5,0). Now suppose one ID is: '56007'. I want to be able to tell how many rows in the entire table have that ID. I know that I can use the select keyword, but i'm wondering if there's another way, because the table i'll be doing this for can be up to 350 Million rows, and the vast majority (probably close to 99.9%) of the rows will have mutually exclusive IDs. I am only concerned with that small percentage of rows that have duplicate IDs
View Replies !
Calculating Totals From Two Tables
I have three tables: invoices, invoicedetails, invoicepayments The fields are: invoices -------- InvoiceNo InvoiceDate CompanyNo invoicedetails -------------- InvoiceNo ProductNo Quantity UnitPrice invoicepayments --------------- InvoiceNo PaymentDate PaymentAmount For each row in invoices there will be 0 or more rows in invoicedetails and invoicepayments, with the InvoiceNo field linking everything together (i.e. one to many relationship between invoices and invoicedetails and invoicepayments). I need a query that will give me a list of invoices that still have money outstanding on them. To manually do this I would loop through the invoices table and for each InvoiceNo I would gather all the matching rows in invoicedetails and invoicepayments. To get the invoice total I would multiply Quantity by UnitPrice for each invoicedetails row. To get the total paid I would add up the PaymentAmount. Then I'd compare the invoice total and the payment total and if the payment total was less than the invoice total I'd know that there was still some money outstanding on that invoice. Now, how do I write a single query to do this? I using MySQL 4.0.20 (unfortunately because I don't control the server I can't upgrade to a version of MySQL that support subqueries). I'm guessing I'll need to use the SUM() function to add things up, and GROUP BY to group the invoicedetails and invoicepayments so I only get one row per invoice. I can get a total for each invoice by using the following query: SELECT invoices.InvoiceNo, SUM(Quantity * UnitPrice) AS InvTotal FROM invoices, invoicedetails WHERE invoices.InvoiceNo = invoicedetails.InvoiceNo GROUP BY invoices.InvoiceNo However I'm at a loss as to how I would modify this query to also total up the invoicepayments to give me a PaymentsTotal and then calculate the difference between the InvTotal and the PaymentsTotal to figure out if there is still money outstanding.
View Replies !
Getting Totals(or Percentage) Of Each Field
Say i have a select statement which selects 5 fields and displays the results as follows: field1 | field2 | field3 | field4 | field5 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 how can i add one more row to the output, which would calculate the total of each field and display at the bottom of the table?? for above example, the output should look like: field1 | field2 | field3 | field4 | field5 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 Total 50 | 100 | 300 | 400 | 200
View Replies !
Computing A Difference In Totals.
I am using php/MySQL 4.0 .. If I had a table consisting of: team l points ------------------------------------- teamA l 15 teamA l 10 teamA l 5 teamB l 5 teamB l 10 teamC l 5 if (mysql_query("SET @rank = 0;", $conn)) { if ($result = mysql_query("SELECT @rank := @rank + 1 AS Rank, team, SUM(points) as ttl FROM table GROUP BY team ORDER BY ttl DESC, TM ASC;", $conn)) How would I get the result below? rank l team l points l behind -------------------------- 1. teamA 30 0 2. teamB 15 15 3. teamC 5 25 I know how to implement the ranking of the teams .. I do not know how to get the point difference for the behind column. Is there a way to set the ranking to handle ties? (Example: 1,2,2,4,5 etc.)?
View Replies !
Showing Totals And Subtotals In One Row
I have a table with the following fields: ContractID | CustomerID | ProductID | Quantity For each Contract there is one record: Who has ordered which product in what quantity. Now I'd like to generate a report that shows: - which products were ordered (SELECT ProductID ... GROUP BY ProductID) - at most (SELECT ... SUM(Quantity) AS Quantity ... ORDER BY Quantity DESC) - and from which customers. (SELECT CustomerID, Quantity ...) GROUP_CONCAT(...)? Subquery? Sample-Output: P_ID - Quantity - Customer's quantities ---------------------------------------- 1230 - 10'000 - A: 2'000, B: 8'000 1240 - 8'000 - A: 7'000, C: 500, D: 500 1120 - 6'000 - C: 6'000 ... How shall I build the SQL statement?
View Replies !
Getting Totals From 5 Tables In One Query...
This is something that has been puzzling me for a few weeks. I have 5 tables in the database that I want the total rows count from. Now I know I could do 5 queries and use 5 mysql_num_rows to return the result but I feel sure that there is a better/easier/more efficient way of doing it. i am guessing that it has something to do with joins but mysql really isn't my thang! I Have tried something like: select count(hotel.id) as atb, count(trainer.rec_id) as det, count(club.id) as ml, count(activity.id) as sl from hotel, trainer, club, activity but that returns the totalled amounts added together which is why I figure a join is needed. My aim is a online stats panel something like: Leisure Clubs Onsite: 2442 Trainers Onsite: 232 Hotels Onsite: 1978
View Replies !
Select Statement, Grouping By Totals
I have an enormous database and I'd like to count how many times a unique record appears, then order the results based on that. For example: select a, b, count(a) AS TOTAL from table GROUP BY a ORDER BY TOTAL DESC; +-----------+------------+----------+ | a | b | Total | +-----------+------------+----------+ | z | 2004-01-14 | 24 | | x | 2004-01-05 | 22 | | b | 2004-02-11 | 20 | ------------------------------------- Meaning z appeard 24 times, x 22, and so on. This only returns the totals, not each row in itself. I need to have each row returned based on the amount of times it appeared. I obviously have to keep GROUP BY in there so I'm unable to ORDER BY TOTAL returned.
View Replies !
Selecting Totals For Multiple Dates
I have a form where a user can input two dates and I want to get a sum of the day's data for each of the days separately. So far the closest I've come is: // to display the total for one day SELECT sum( hplmnmoc ) FROM `inRtccCallType` WHERE host='wilsle03' AND date='2007-05-25' OR // to display the total for all days SELECT sum( hplmnmoc ) FROM `inRtccCallType` WHERE host='wilsle03' AND date BETWEEN '2007-05-24' AND '2007-05-31' With what I have so far I can either display one day's total or else a total for the whole period. Can anyone tell me how to get the totals for each day individually without having to perform multiple queries.
View Replies !
Totals Query Based On Days
If I have a table with a ProductID, Quantity, & DateTime field, & would like to have the sum of the Quantity calculated per product per day with blank days being accounted for even if zeroed out, how would I go about accomplishing this in one query? Example result for ProductX:
View Replies !
Fiscal Year Totals - How To Calculate?
I am given the month number for the fiscal year. For exmaple, "4" indicates the fiscal year begins April 1 each year. April 2, 2005 would be Fiscal Year 2005. March 30, 2005 would be Fiscal Year 2004. With the following table structure: TABLE_A id date amount My current set up is like this, based on calendar year: PHP // get a list of years in the db $years = $dbh->getCol("SELECT DISTINCT(YEAR(r.date)) FROM table_A r ORDER BY YEAR(r.date) ASC"); foreach ($years as $s) { $yearSum = $db->getOne("SELECT SUM(r.amount) FROM table_A r WHERE YEAR(r.date) = '$s'"); //echo something here } ISSUE1: I need to calculate the total for each fiscal year. For example, April 1, 2004 - March 31, 2005. AND April 1, 2005 - March 31, 2006, and so on and so on for all years. ISSUE2: I need to calculate the total for each MONTH within each fiscal year. For example, during the fiscal year April 1, 2004 - March 31, 2005, what was January's total, Feb's total,... For each fiscal year.
View Replies !
Gathering Totals From Multiple Tables
I've got a little bit of an issue which I need a little bit of mysql-guru help with. I want to get the top 5 users who have authored the most articles, and how many articles each has authored (total). The problem is, I have 3 tables in which I store the articles. I've got articles_faq, articles_kb, and articles_ref. Each of these has an auto_increment field 'id', and an 'author' field (there's obviously more fields, but they aren't relevant). How would I get this data in a single query? Is it even possible using MySQL? Although I'm improving my MySQL skills and knowledge quite a bit, this is beyond what I'm capable of, that's for sure.
View Replies !
Query To Return Totals Of 1-5 Votes, Even If That Number Is 0
query to return totals of the votes: SELECT count(good) as numgood, sum(distinct good) as scoregood, sum(good) as totgood from survey where jobclass = 'Salaried' group by good That returns this: numgood | scoregood | totgood 27 | 4 | 108 70 | 5 | 350 What I need are results that include no votes cast for the other values: numgood | scoregood | totgood 0 | 1 | 0 0 | 2 | 0 0 | 3 | 0 27 | 4 | 108 70 | 5 | 350
View Replies !
Viewing Date Range Then Adding Column Totals?
this is probably my most complex question to date. Basically i have a table that stores order information for products. What i need to do is: - Specify a Date range - Count number of rows in that range - Get column totals for that range - Return Array with column totals eg, if the array was named $total, $total['column1'] would be the column 1 total :) This is a large table with many columns so here is what i had planned: //OPEN CONNECTION HERE, SET DB //First query gets date range: $result = mysql_query("SELECT * FROM D_Orders_Columbus WHERE odate > '" . $startdate . "' AND odate < '" . $enddate . "'"); //now we get number of rows: $num_orders = mysql_num_rows($result); After that i get stuck, i need it to ADD the column values together, for this i assume i will need to set the column types to 'SMALLINT' (i dont assume anyone will order 32000 items :p). How can i get mysql to total all the columns that can be (eg. have number types) and then return an array with the totals?
View Replies !
How Do I Generate Results Based On Totals Of Another Table But For 1st Table?
This is what I want to do: 1- I have Two tables: polls_created and votes 2- Table polls_created is like: poll_id owner poll_subject 3- Table votes has the votes issued for a given poll, like this: vote_id poll_id vote vote_date So what I need to do is to look at these 2 Tables and generate results based on values of these 2 tables. How do I then generate this result: MySQL Code: SELECT poll_id, owner, poll_subject, COUNT(vote_id) AS number_of_votes FROM polls_created, votes "sorted by polls that have gotten most number of Votes" Of course "sorted by polls that have gotten most number of Votes" is not real MySQL
View Replies !
Querying For Transaction Totals And Last Transaction Date
I have a list of currency transactions made by users. I need to generate a list of users along with their transaction total (sum for each user) AND the date of their last transaction. Sound doable? MySQL 4.1 Data looks like this: user, amount, date ============== 1, 50, 2003-11-23 2, 34, 2004-10-04 3, 45, 2005-08-30 3, 98, 2006-04-02 3, 76, 2000-02-03 2, 91, 2000-12-04 1, 11, 2003-11-05 3, 22, 2003-03-06 4, 34, 2006-03-07 5, 45, 2006-06-24 I figure I can group by userID but how do I get the date of the most current transaction? Using the data above, the query would return: 1 (user) 61 (subtotal) 2003-11-23 (last transaction) 2 (user) 125 (subtotal) 2004-10-04 (last transaction)
View Replies !
Eliminating Values From A List / Finding Non-existing Values
I have a list of thousands of values, some of which exist in the database, and some of which do not. I'd rather not loop through the list, SELECTing each item to test its existence, and was wondering if there's a more elegant way to do this using an SQL statement. For example: Database: 1 3 5 6 List: 1 2 3 4 5 I'd like MySQL to return to me the following, a list of the non-extant items I passed: Result: 2 4
View Replies !
"Totals" Row For Columns
Looking for the result below from the sample table listed. Using MySQL 4.0/php. ROLLUP is not supported in my version of MySQL ... table1 -------------------------- Person l Units l Amount -------------------------- person1 l 7 l 11 person1 l 8 l 11 person2 l 13 l 6 person2 l 13 l 7 person3 l 11 l 7 SELECT Person, SUM(Units), SUM(Amount) FROM table1 GROUP BY Person person1 l 15 l 22 person2 l 26 l 13 person3 l 11 l 7 "totals: l 52 l 42" = desired result
View Replies !
Inserting Into A Table Static Values And Values From An Existing Table
I am designing a content management system for my work-term and I basically need some help in data insertion. What I have is two tables, each with different field (Column) lengths. I am taking data I store in what is a mysqllate table ( basically mysqllate/default information), and appending it to another table which will be an actual production table that will be used in the website. This table still has 3 more fields so basically this is how it goes: mysqllate table: DOCID int(11) No DTID int(11) No ID int(11) No requirements text latin1_swedish_ci No reqd varchar(100) latin1_swedish_ci Yes NULL date varchar(100) latin1_swedish_ci Yes NULL Production Table: RID int(11) No DID int(11) No DTID int(11) No ID int(11) No req mediumtext latin1_swedish_ci Yes NULL reqd varchar(45) latin1_swedish_ci Yes NULL date varchar(45) latin1_swedish_ci Yes NULL comply varchar(45) latin1_swedish_ci Yes NULL notes mediumtext latin1_swedish_ci Yes NULL Therefore as you can see RID, comply and notes, are to be static values determined before the insertion of the data. I just do not want to have to query 900 rows of mysqllate info through PHP append the static values and then re-insert there has to be an easier way, perhaps through a view..
View Replies !
Only Select Values Where Values Are The Same
I'm making an image gallery where the user should be able to edit lots of pictures at the same time. So if the user has 50 pictures of the sun, she can select them, and type in "sun", and mark all images as a picture of the sun. There are a couple of fields that can be the same for each image, and I would like to check if any of these fields are the same for every selected picture. If there is, that value should be prefilled, but if just one of fifty images has a different value, nothing should be displayed. Is there a way to solve this in SQL?
View Replies !
Getting Values From Row
One way that I determine the most recent insertion into a table is to get the highest id value in the id field. Using the auto increment function whenever a new record is inserted in the table, the hightest id number by default is the most recent submission into this table. I would like to create a query that pulls up all the data from the row with the highest id number. I know that to get that number, I can use the max(id) approch, but I don't know how to enter this into a sql statement, and get the other values that attach to it. I tried "select name from table where id=max(id) but I get a group error reply. What can I do to get the values I'm looking for?
View Replies !
Two WHERE Values
I dont even know what to search for on this. Is it at all possible to have two WHERE values on a query? Would I maybe put an and symbol or something? what is the proper syntax for something like this, or is it even possible? SELECT * FROM Hotlines WHERE date_opened='20050203'
View Replies !
Get Rid Of Values
This is it, say i have six numeric fields to fill in. What i need is to get rid of the biggest and smallest numbers among these six fields, so i can take into account just the middle four left fields. It's a system for ranking persons performances. For example: field#1= 6.3 field#2= 2.3 field#3= 7.5 field#4= 4.9 field#5= 8.3 field#6= 4.5 and i need to get rid of the biggest and lowest entered values, in this case, i should get rid of fields #2 and #5
View Replies !
Sum Values
Let's say I have a messages table with a points column, where points may be 0, 1, 2 or 3. Is there a way to selectively sum the values for each id except for points equal to 3 ? The query below sums everything, even when points equals 3. SELECT id, SUM(points) AS total_points, COUNT(*) AS num_messages FROM messages GROUP BY id
View Replies !
In Between Values
i got a table [code] type points ... 4 100 5 200 6 300 ... now..lets say i got 150 pts...so that will fall to type 4... so whats the proper sql statement for that?
View Replies !
Max Values
is it possible to set a maximum value for a column? I want to have a max value of 100 so if the current value is 98 and the query tells it to update by a value of 3, it will still update it but only up the maximum.
View Replies !
Sum Of Values
I'm working on something that I don't have much experience with. I have the following query: select item_no, sum(price), count(*) from production where date > '2006-11-15' group by item_no; which returns: item_no --- sum(price) --- count(*) 27714 --- 327.6 --- 6 29582 --- 269.64 --- 4 38599 --- 476.34 --- 6 Is there a way that I can get the total of the sum(price)? Do I need to do this in PHP?
View Replies !
First And Last Values
Is this the most efficient way of performing this action? SELECT (SELECT `Date` FROM mytable ORDER BY `Date` LIMIT 1) AS FirstDate, (SELECT `Date` FROM mytable ORDER BY `Date` DESC LIMIT 1) AS LastDate
View Replies !
Values From Different Rows
I have Orders and Payments. Sometimes the Orders do not have Payments. So, I decided to use LEFT JOIN. Furthermore, some Orders have 1 related Payment, and some Orders have 2 or even more related Payments. Now, I want to combine the sum of all Payments and the last Payment in one row. select Orders.SeqNr, sum(B1.Amount) Total_paid, max(B2.SeqNr) Last_record, B2.Amount Last_amount from Orders left join Payments B1 on B1.FK_Orders = Orders.SeqNr left join Payments B2 on B2.FK_Orders = Orders.SeqNr group by Orders.SeqNr If there is only 1 payment per order, then I will see perfect results. However, if there is more than 1 payment per order, then the results are wrong... The column Last_amount does not contain the amount which belongs to the column Last_record.
View Replies !
Find Value Not In Between Values.
I have a SQL question I am having trouble with. We have a table, with a column that contains a code. These codes start at '00001', '00002', '00003', '00004'... and so on. If the record containing '00003' as it's code is deleted from the table, is there an SQL statement that can be used to find the first missing record. I guess what we are looking for is the lowest value of CODE not in the list of values between two values, such as in the case '00001' to '99999'.
View Replies !
128 Bit Values As Primary Key
Suppose that 'bigkey' is a column capable of representing the value of a 128-bit unsigned integer in some efficient fashion... maybe as a CHAR or VARCHAR field big enough to hold its decimal or hex representation, or maybe some other datatype, and ${128bitvalue} is a representation of it acceptable to MySQL. Suppose 'smallkey' is an UNSIGNED INTEGER column. Which of the following two queries is likely to be faster and more efficient? SELECT somecolumn, anothercolumn FROM foo, bar WHERE foo.bigkey = ${128bitvalue} AND bar.bigkey = ${128bitvalue} SELECT somecolumn, anothercolumn FROM main, foo, bar WHERE main.bigkey = ${128bitvalue} AND foo.smallkey = main.smallkey AND bar.smallkey = main.smallkey If MySQL had a native 128-bit integer type, it would probably be a no-brainer to just use it directly. Unfortunately, as far as I know, there's no way to store a 128-bit integer in a MySQL column without converting it into some alternate representation first. Assuming that I'm still better off using the 128 bit value's representation directly as the key instead of joining it via a third table, what's the most efficient way to represent that 128 bit value to MySQL? CHAR(39)? VARCHAR(39)? Some other format?
View Replies !
Getting Values From Row With The Largets Id
One way that I determine the most recent insertion into a table is to get the highest id value in the id field. Using the auto increment function whenever a new record is inserted in the table, the hightest id number by default is the most recent submission into this table. I would like to create a query that pulls up all the data from the row with the highest id number. I know that to get that number, I can use the max(id) approch, but I don't know how to enter this into a sql statement, and get the other values that attach to it. I tried "select name from table where id=max(id) but I get a group error reply.
View Replies !
Add Values To Table
I have a table tblMailingList. It has 3 columns; Name, EmailAdd, AutoID. I am using mysql> INSERT INTO pet -> VALUES ('name1','u@me.com'); but I am unsure as to what to do for the AutoID field. If I use the example given, the error message says that the number of fields is different. How do I get this info into my table?
View Replies !
Number Values
I need to add up the total of the value of people. In other words if Family 1 = 3 people Family 2 = 2 people I need total 5 people PHP Code: $result1 = mysql_query( "SELECT *, DATE_FORMAT(time,'%b %d, %r') AS time FROM rsvp ORDER BY people DESC "); $number_people = mysql_fetch_array( $result1 );
View Replies !
Adding Row Values
Is there a way to do this wihtout GROUP BY that will SUM JobPostView as a single result? SELECT JobPostView, SUM(JobPostView) FROM JobPosts2 WHERE JobPosts2.JobID BETWEEN 1115 AND 1208 GROUP BY JobClass
View Replies !
Aes_encrypt Values
I guess if explain my situation first it should help a little better... I have a table with some empty fields and some numbers I wish to encrypt. I perform a select statement to check how many fields are not empty (I get 56), e.g "SELECT number from customer WHERE NOT number = '' "; I then encode this using AES_ENCRYPT(number,keystr) Then try the count query again as written above but this time I have fewer entries (I get 3) that are not empty. Which is odd because I can then decrypt these records and I get my 56 records back again. Any ideas why this may be? I am using tiny blob as the field by the way, do I need to perform some sort of php string function on the numbers before storing them do you think?
View Replies !
Swap Values
what the best method is for swapping column values between 2 rows in a table is. Right now I'm using a method with 3 separate update statements, like so... UPDATE table SET order='-1' WHERE order='$id1' UPDATE table SET order='$id1' WHERE order='$id2' UPDATE table SET order='$id2' WHERE order='-1' but I have a feeling there must be a better way to do it.
View Replies !
Column Values.
is there a way i can create a column that returns result the biggest value of other columns? ie. table foo has following fields, all INT, except name VARCHAR name list A B C so if name=joe has A=5 B=7 C=9 , i want list=9 is it possible in mysql to create such variable columns? or do i need to make a perl script to update the table with the info based on what I have on A, B, and C.
View Replies !
Checking 2 Values
lets say I have a table that contain the following: nid tid --- --- 1 2 1 14 2 3 2 14 i.e it has 2 tid values for each nid. Now I want to check in my WHERE statement if nid = tid1 AND nid = tid2
View Replies !
Repeated Values
Currently I'm trying to select and display only repeated results form database. In my table I have these fields 'id', ''Computer_name', 'Operating_System_Key'. I want to select and display once only repeated values from 'Operating_System_Key' column.
View Replies !
Field Values
I have ~600 rows of data in my table and I was wondering what would be the easiest way to change all the values in certain field from positive to negative?
View Replies !
ENUM Values
Is there a way to get values for an ENUM field from another table in the database? What is the SQL syntax for that.In other words, I have a table called "products" which has a field called "category". The field category should be an ENUM composed only of values stored in the category table. Basically, can we use a SELECT statement within an ENUM description?
View Replies !
Grab Values
I have a list of names in a mysql database. The interface I use to communicate with it is php. i.e. 1 | Tom 2 | Mary 3 | Harry 4 | Sally I would like to select these names from the database and put them into a form that I can use through specifying them as as a single value to be used later in the script: i.e. $allthenames = " 'Tom','Mary','Harry','Sally' "
View Replies !
Return All Values
On my user registration processing page I need to test the visitor's proposed login name against all registered login names to ensure uniqueness. My two MySQL books are unclear, so I have a couple of questions: 1) How do I compose a query that returns all the values in a single column (username, in this case), so that I can test my new username against them to ensure uniqueness before I write the new username to the db? 2) Does MySQL provide a quick and dirty way to do such a thing, or will I manually loop through all returned values to test for uniqueness?
View Replies !
Inserting Values
I need a big database only 2 table. i have been able to do the tables and all not problem. I can add and delete no problem. One of my problem is I do not want duplicate entries for one field. The problem is because database is so big and the entries have duplicats i have to add them in 1 batch or I will be adding then one by one forever. Is there a way around this? Second I have an entry like below INSERT INTO `table` VALUES ('2005.02.13 19:06:35', '853edbd1c9fdbcf0a9ff5cbf5e347267', 'sajdhs'dhs'); Second I cannot put the above in mysql unless as it throws and error becuase of the ' used in thrid entry. I need these entries to go in as they are.
View Replies !
Inverting Values
The problem is as follows: let's suppose we have a table with a column that can contain 'yes' and 'no' and we need a query that will 'invert' these values in all the records. So, each 'yes' becomes a 'no' and each 'no' becomes a 'yes'. And we need to do this with one MySQL query.A friend keeps claiming that there are at least 5 ways to do this but I can't figure them out... Heck, I can't figure out one way to do it.
View Replies !
Mediumint Values
I am just curious to know whether it is possible to write negative numbers to an integer field such as mediumint? I have always used these field types with positive integers so would writing, -100 actually cause a problem?
View Replies !
Multiple Values
I'm working on an application that allows people to publish training information to a database. They have an option to specify how the course is delivered i.e Classroom, Web Based, etc. This is a checkbox value so a single course can be delivered in multiple ways. But what's the best way to store this in the database? The immediate solution that comes to mind is to place all the selected deliveries as comma separated values in the same column. In the corresponding Java object the values will be stored in a String array, and since there won't be a huge number of delivery options it won't be difficult to manage. Is this typical or deemed or a good design though?
View Replies !
Increment Values
When using an auto-increment field, can anyone tell me how mysql remembers what number it is on, and if there are any cases in which it would forget.eg, if the max ID of somthing is 8, and then you delete the row with an ID of 6, it still uses 9 next. If you then delete the 9 row, it still uses 10. I just want to make sure that when i remove some records in one of my tables, the associated data cant then be linked to a new record which has been given the one records ID.
View Replies !
|
TRACKER
