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 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 !
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 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 !
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 !
Percentage
I have a table of score which are 1's and 0's. How would I go about writing a query to get the percentage of 1's against the toal number of records?
View Replies !
Get Top X Percentage
I am trying to do a report that tells us our top selling products in a table where I already have the data compiled: product_code, product_name, total_dollars I would like to be able to query and say give me the top 50% of our products. Finding the top 50% in a dollar value is easy. Applying it to the table to return the best selling products until the sum of their total_dollars is equal to 50%.I'm usually pretty bad with mysql math, so I was hoping someone could help. I'm sure this is a fairly common mysql procedure, but I've never done it before...
View Replies !
Percentage On The Fly
My table now looks like this : vote_id*, voter_id, vote_date, voter_ip, votee_id, title_id. I have the first part of the query working ok SELECT votee_id, title_id, COUNT( vote_id ) FROM jos_comprofiler_plug_fun2 WHERE votee_id =79 GROUP BY title_id; Nice and simple it gives me the number of votes each title has according to a specific votee: votee_id, title_id, COUNT( vote_id ) 79, 1, 1 79, 2, 1 79 , 3, 4 79 , 4, 2 79 , 5, 1 79 , 6, 1 But how do I change this query to give me the percentage of votes each title has according to a specific votee?
View Replies !
Calculating A Percentage
I'm working on an adp file and I'm trying to do is calculate what percentage one figure is of another figure in a view. In access I would simply do it like this: [FirstField]/[SecondField]*100 But this doesn't seem to work. My exact code is as follows: SELECT Product, Quarter, TotalPlanProductSpend, TotalPlanSpend, TotalPlanProductSpend / TotalPlanSpend * 100 AS PercentOfTotal FROM dbo.Qry_RptPlanPRoductSetup
View Replies !
Percentage Of Votes
i made a script for my buddy that his clients can vote and wright comments on his service. in my database i have 3 fields vote comments web so vote is the votes rated and web is the rates rated for the web site. now i want to extract the total percentage of votes? i have a start..from searching on Google mysql percentage SELECT (SUM( votes ) / COUNT( votes )) *100 percentage FROM `votes`
View Replies !
Returning A Percentage
is it possable to return ONLY 20% percent of the overall possable returns in a query? e.g. i have 100 possable returns and i only want 20% of them. So i'd receive 20 returns. What i'm trying to do exactly is return the latest 30 entries and then the next 20% of the possable entries. i'm working with a database that has thousands of possable returns but i don't want all of them.
View Replies !
Percentage Query
I would like to build a query that returns a scoring percentage on several fields in one table. The result if every condition is true, should be 100% and for instance if one condition is not true 95 percent and so on. I would like to have returned the id's and the scoring percentage. Is there a clever way to do a query like that besides going through the database several times like first selecting on a 70% score then on a 75% then.... and finaly he 100% score?
View Replies !
How Do I Increase A Cost By A Percentage?
currently training on SQL - doing alright so far until this sub-question... Migrate all data from the JOB_COST table into the TOTAL_COST table. For those records that match the COST_ID, update the UPPER_LEVEL and increase the cost by 15%, otherwise insert the records.
View Replies !
Calculate Series Percentage
Is there a way to calculate the percentage of a series of values in MySQL? I need to have the total of sum in order to calculate the percentage in each row. How can I do this in the following query? SELECT product.productId, product.name, COUNT(product.productId) AS count, SUM(invoice.value) AS sum FROM product, invoice WHERE product.productId=invoice.productId GROUP BY product.productId
View Replies !
Query Result Percentage
how I might be able to get each record's query match percentage? What I mean is, say your query contains multiple criteria, of which, a variable number of fields per row will match. You see this often with dating services, e.g. name, height, weight, hobby, etc. Not all fields will match so a percentage is provided, then you can sort by percentage. Is there a mySql function that can do this or must this be done the tedious way - like using PHP to test each value against each field's data per row, then updating, then sort
View Replies !
Percentage Correct Calculation
I"m not sure this is the right way to do things - but it's the web site database I do have now that I"m using. It's a pretty simple 'pick the winners' database Table - Picks userID gameID pickedteamID gameresults gameID winnerID What I need is a query that will return the total number of picks for a user as one column and a count of the correct picks...(ideally i want a percentage) - say i've picked 16 games and 8 correct...i would want the result to be jemagee 8 16 .5 (percentage correct) THe problem is of course I want to select ALL users and order descending by the percentage count so I can list a standings. Is this doable in mysql alone? Did it make sense.
View Replies !
Finding Percentage Of Column
I attempting to add a column of data that calculates the percentage a data element comprises. The following is an example of the data I have so far: Diagnosis | COUNT(diagnosis) ----------+----------------- cough | 10 acne | 20 diabetes | 20 This is what I'm trying to produce: Diagnosis | COUNT(diagnosis) | Percentage ----------+------------------+----------- cough | 10 | 20% acne | 20 | 40% diabetes | 20 | 40% This is what I've attempted to do but get a "invalid use of group function" error when I try to embed the COUNT function inside the SUM function. SELECT diagnosis, COUNT(diagnosis), (COUNT(diagnosis) / SUM(COUNT(diagnosis))) AS "percent" FROM diagnosis_table.all_diagnosis GROUP BY diagnosis; The value I'm attempting to calculate in "SUM(COUNT(diagnosis))" is the total summation of all the cases in column "COUNT(diagnosis)." Once I have that value, I divide it into the value "COUNT(diagnosis)" to determine the percentage a particular diagnosis comprises out of the total in that table.
View Replies !
Percentage Of 2 Year Data
I am having the toughest time trying to figure out how to get a percentage from my tables. 1. I Have 12 tables from 2005 named January2005, February2005 etc. up to December2005. I also have 12 tables for 2006 name January2006 etc. 2.In these tables is a column named Result and the value is either Won or Lost. I am counting how many times is says Won or Lost and getting a Total for each Month. "Select Count(Result) From January2005 Where Result = 'Won'; 3.I am getting a percentage for 2005 and 2006 just fine. SELECT Round(((WonTotal)/Val((WonTotal)+(LoseTotal))*100)) FROM WinTotal, LoseTotal; This is the problem: I need to take 2005 and 2006 and combine them to get a total percentage of Won.
View Replies !
MySQL Column Value As Percentage
On my site, I log all visitors to a MySQL database. One value in the table is their browser, which is either IE6, IE7, Opera, Konqueror, Safari, Firefox, or Other if it isn't any of those. I want to create a list which shows what percentage of users use each browser. For example: Firefox: 31% IE7: 29% IE6: 18% Safari: 8% Opera: 5% Other: 5% Konqueror: 4% Those are completely made-up figures, by the way.
View Replies !
Wildcard Return Percentage Match
I am trying to write an query which will return the percentage match of a wildcard search in mysql.I am using the LIKE command. eg ( Word in table= Barack Obama Word passed to query= Barack percentage match:50%(I know this percentage isn't correct) ) Is this possible in SQL?
View Replies !
Working Out The Occurrence Percentage In A Column
I wondered if anyone knew if it is possible to work out a percentage of the occurrence of a text value in a column in a MySQL database. I have a compliance column with simple yes/no answers and need a percentage for the amount of times yes is entered.
View Replies !
Percentage Calculation Added To Table
I need to add an extra column to this view which will calculate the percentage from the grand total of the number of matter column (so its number of matter/grand total of number of matter *100). SELECT COUNT(matter.matter) AS `number of matter`, matter.matter, matter.county FROM matter GROUP BY matter.matter, matter.county My server version is 5.0.27-community-nt
View Replies !
Calculate Percentage In A Single Sql Statement
i am trying to calculate percentage of student came to computer laboratory in a single sql statement but no luck. SELECT f.facultyInitial, COUNT(*) AS TOTAL FROM attendance att INNER JOIN academic a ON att.academicNo = a.academicNo INNER JOIN program p ON a.programId = p.programId INNER JOIN faculty f ON p.facultyId = f.facultyId WHERE YEAR(att.attendanceDate) = 񟭆' GROUP BY f.facultyInitial ORDER BY f.facultyInitial alright, sql statement above will produce an output like below: facultyInitial | TOTAL --------------------- Account | 2 Civil | 1 FITQS | 3 what i want to do is, to put a percentage at the right side of the TOTAL column. facultyInitial | TOTAL | PERCENTAGE ----------------------------------- Account | 2 | Civil | 1 | FITQS | 3 | here is my example to produce a percentage but it does not work SELECT f.facultyInitial, COUNT(*) AS TOTAL, ROUND(COUNT(att.attendanceId)/TEMP.TOTAL_ROWS * 100, 2)) FROM attendance att INNER JOIN academic a ON att.academicNo = a.academicNo INNER JOIN program p ON a.programId = p.programId INNER JOIN faculty f ON p.facultyId = f.facultyId, (SELECT COUNT(*) AS TOTAL_ROWS FROM attendance TEMP WHERE YEAR(att.attendanceDate) = 񟭆') WHERE YEAR(att.attendanceDate) = 񟭆' GROUP BY f.facultyInitial ORDER BY f.facultyInitial
View Replies !
Returning Relevane Of Match Queries As A Percentage
I'm interested in exploring the match query for a site in doing. can u return the relevance of a match query in percentage form? Also where are there good tutorials/resources on getting the most out of match queries. for example im looking for more info on assigning different weightings to specific words if that is possible?
View Replies !
A Query To Select A Column When A Percentage Of Values Non Zero?
I wounder whether some of the experts out there might be able to help me with a problem I'm having. I do not know whether this is possible or not... I have a large table of stock price data which is straight-forward enought. I can select prices based on a ticker and date ranges. However, what I'd like to do is to select prices only when, say 75% of them are non-zero (with the goal of eliminating new/suspended/delisted stocks). Of course I could just select where price > 0, but then I might get only a few rows where this is the case. What I would like to do is always get the full date range of prices, but only if >75% are there.
View Replies !
Query Pages Printed By Percentage Of Total?
I'm very new to MySQL and have a query I'm trying to achieve, if anyone can point me in the right direction, I would greatly appreciate it. I have a table created to store print job logs from a print server: Table = printLogs logNum mediumint(8) logDateTime datetime docNum tinyint(3) docName varchar(200) owner varchar(25) printerName varchar(25) printerPort varchar(50) sizeInBytes varchar(50) pagesPrinted smallint(10) What I'm trying to do is find a query that, based on a 'printerName', will display 'owner' and percentage derived from the total number of pages printed to that 'printerName' for a given date range. Something like this: +--------+--------------------------+ | owner | Percentage of total jobs | +--------+--------------------------+ | Mary | 50% | | John | 30% | | Kim | 10% | | Sue | 10% | +--------+--------------------------+ Each row of the table is a different print job and can be one of about 20 different owners who have printed any number of pages to any of 10 printers. The main reason for the query is to be to determine which group/owner uses each printer the most.
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 !
"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 !
Retrieve Field Value When Submitting To Table Auto Increment Primary Field Value
In my form, I write to four fields in the table, (fields 2,3 4 and 5), which creates a new record. the first field of this new record is an auto-incremental field, giving a unique id. How can I write to the table and at the same time retrieve that unique id accurately, even if more than one person is performing write tasks simultaneaously? My first thought is that if I grab the previous records id and add 1, then this may not match my newly inputted record, given that it may take a few minutes to add the record and someone else could have been there before me with a different record. (I hope that makes sense). Then I thought that on opening the form, it could write to a new field inputting my loginID. Then I can retrieve the unique auto-increment number before then filling out my form and over-writing the loginID that I had previously entered. Sounds too convoluted to be the best way.
View Replies !
Extracting Field Data And Putting Each Field Into A Shell Variable?
In a MySQL query on the database (one table with 15 variable length fields, I want to put each field into a Bash variable so that I can handle each field as an entity. The query I have is something like this: mysql -e "use $database; select field1, field2, field3..., from Table1 where fieldN like '%something%';" can I / How do I do something like: mysql -e "use $database; select field1,..., var1=field1, var2=field2,....;" do stuff with var1, var2..., done
View Replies !
Can I Store The Sum Of Row's Child Rows' Field In A Parent Field?
Sorry if this has been posted before; I am not sure what to search for. I have a table of users, `users`. In the table `users` there is a row which stores clicks, `users`.`clicks`. Each user has it's own referrer, `users`.`referrer_id` which refrences the referring user's id. In the referring user's row, I would like a field to store referral clicks, `users`.`referral_clicks`. I would like `users`.`referral_clicks` to be updated each time that a child user's `users`.`clicks` field is changed. Is this easily possible in MySQL?
View Replies !
Regular Expression :: Search In One Field Concatenate Result In Other Field
Here is a tough one and it might not be possible but I hate to give up without a fight: I'm trying to make a query that runs through a database and searches for a regular expression in one field, then concatenates what it matched to the end of another field. Is this possible or do I have to concatenate the whole field? Can you use regexp with replace and concat at all or is it only used in the where clause? Normally I'd use a program to hold the value it matched and then run it in a different line but for this I need to use only mySQL. It kind of implies a reversed syntax because it needs to search for the string before it updates.
View Replies !
How To Convert A Varchar Field Into Proper Mysql Date Field?
I have a database in which the date is stored in varchar field in a following format: d-m-Y (06-08-2007), now the problem is that I want to change that field into mySQL date field as well as convert my older dates into MySQL date format i-e Y-m-d (2007-08-06).. There are about 300 old entries..is there a way I can do that automatically without manually re-entering the dates again?
View Replies !
|
TRACKER
