How To Sum Two Aliased Values In A Query
I need to sum two aliased values in a query in order to order the results.
The query looks like this:
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Distinct Values In Mysql Query
I have mysql query but i am not sure about it,bcoz it return many rows.. I want like distinct values from three tables with ic number. Which is posted by user... "SELECT distinct loguser.icnumber,access.acccode,dealerdtl.name,loguser.fullname,access.period,loguser.staffcode, DATE_FORMAT(access.actdate, '%d-%m-%Y ')As formatteddate1, DATE_FORMAT(loguser.creationdate, '%d-%m-%Y %H:%i:%s')As formatteddate FROM dealerdtl,loguser,access where access.icnumber=loguser.icnumber AND loguser.icnumber = '$custic'"; This query return many rows i dont know why, i want only one row which is inserted by the user as customer id.
Reference To Already Retrieved Values In Query
As a simple example, say there is table 'namelist' with column 'names' as char(20). I would like to do something akin to: select namelist.names as mynames, left(mynames,2) as initials; In this example, I could just do left(namelist.names,2), but in more complex cases a value retrieved may have had a more complex logic behind it, e.g., if a bunch of nested if() statements. It would seem logical that if a value is already retrieved then I should be able to refer to it within the same query, assuming that the original value does not need to be recalculated. I know I could use a view to do this, but it adds another layer of complexity, and I am not sure that the values would not be recalculated each time the value is needed. If someone could point out to me the correct terminology for this kind of thing I believe I should then be able to look up the information myself. My efforts to find this information on google and the mysql website were unsuccessful.
Query For Counting Unique Values
I'm trying to find out if I can construct a query to MySQL that will return the number of unique values in a given column. The reason is because I have a column that just contains just the YEAR of a given report. I want to see how many different values populate the YEAR column through the entire query result so I know how many tables to render on screen (one table for each year).
NULL Values In SELECT Query
I need to know how to push null values to the bottom of a query sorted by ASC. I know if you sort by DESC the null values are placed last. How can I push the null values to the end of a query while still sorting using ASC? Anyone?
Accumulating Values Inside A Query
I only have experience of simple queries, and this one is a bit beyond me. I've done some research, and I think it might be possible to do within a single query, but I'm not sure. I have a single table. It contains columns (among others): date, number. I want to produce a table of DATE, and new_number_count, where 'new_number_count' is the number of numbers on day with date DATE that do not have rows on previous DATEs. For example, if this is my table:
Update Query Where Values Will Come From Other Table
I'm creating an update query which the value will come from another table. I have here my current query which unfortunately makes the system hangs. Probably because of the query itself is not properly coded. update boxes b inner join messages m on b.ctnnumber = m.ctnno set b.consigneerecv = m.CName, b.consigneerecvdate = m.DateRcv, b.phrecventered = "Y", b.PhilStatus = "delivered", b.prevreleasestatus = b.releasestatus, b.releasestatus = "delivered", b.PhilStatusDate = m.smsrecvdate, b.phdelprice = "0.00", b.phdelamt = "0.00", b.recvrelation = m.Relation, b.APRecventered = m.smsRecvDate where b.consigneerecv = '' or b.consigneerecv = 'NA' or b.consigneerecv is null; I'm thinking revising it so that it will not cause the system to hang but I don't know how. Guys please help me with this one. I also have this another idea which probably will not work. My idea was something like this: Update table1 set table1.column1 = (select table2.column1 where table2.column1 = table1.column1), table1.column2 = (select table2.column2 where table2.column1 = table1.column1), .....
How To Generate Sequence Values During Query
I have query like below: SELECT part_no,part_nm,qty FROM tb_stok_out ORDER BY part_no as result: part_no part_nm qty aaa asdfd 3 abab sdfsdf 4 abab adfdf 5 Is it possible in mysql to generate sequence number using query, so the result will be like below : 1 aaa asdfd 3 2 abab sdfsdf 4 3 abab adfdf 5 ... etc
Difficult Query - Need To Group Results By Id And Sum Values
I have got the meat of this query done but I am facing a problem. I am doing a VAT analysis whereby I have every shoe that is over size 7 I pay tax on and every shoe below that I don't pay tax on. I also pay tax on accessories. What I want the query to return is this: Date | ShopperID | VATable Amount | Non VATable Amount| 2006-3-1 | 802135 | 146.95 | 54.00 | Basically each shopperID will only appear once which is why I am grouping the results (which I have managed). But I also need it to total up all the VATable amounts that it finds too for both the fields on the right above. I can get the date and order number without difficulty and I have managed to get the data like this so far where I have the same ShopperID where a shopper has bought more than 1 product: +------------+----------+-----------------+---------------------+ | Date | ShopperID| VATable Amount | Non VATable Amount | +------------+----------+------------+---------------+ | 2006-09-04 | 805284 | 0 | 64.00 | | 2006-09-04 | 805287 | 2.95 | 0 | <-- Here a | 2006-09-04 | 805287 | 3.25 | 0 | customer has | 2006-09-04 | 805287 | 3.45 | 0 | bought 4 products | 2006-09-04 | 805287 | 4.95 | 0 | - I need total | 2006-09-04 | 805327 | 0 | 53.95 | under each | 2006-09-04 | 805335 | 0 | 58.95 | ShopperID | 2006-09-04 | 805414 | 0 | 64.95 | | 2006-09-04 | 805414 | 3.25 | 0 | | 2006-09-04 | 805414 | 0 | 64.00 | | 2006-09-04 | 805414 | 0 | 69.00 | | 2006-09-04 | 805423 | 0 | 64.95 | | 2006-09-04 | 805423 | 0 | 69.00 | | 2006-09-04 | 805423 | 0 | 64.00 | | 2006-09-04 | 805423 | 3.25 | 0 | +------------+-----------+----------------+--------------------+ Heres the query currently getting these results (without the GROUP BY ShopperID). SELECT Date,orders.ShopperID, CASE WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9][7-9]( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]10( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]11( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',1) REGEXP 'Insole|Helmet|Laces|Wheels|Removal|Protection|Bag' THEN items_ordered.price -- If it is an accessory add VAT ELSE 0 END AS 'VATable Amount', CASE WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^1-9][1|2|3|4|5|6]( )?(' THEN items_ordered.price -- If size 1 - 6 add price to other column WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(UK)?( )?( )?Kids( )?( )?(UK)?( )?12|13( )?( )?(' THEN items_ordered.price -- If size kids 12/13 add price to other column ELSE 0 END AS 'Non VATable Amount' FROM items_ordered,orders WHERE (items_ordered.ShopperID = orders.ShopperID) AND (Date >= ��-1-30') AND (Date <= ��-9-31');
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.
Showing Query Result With Default Values When No Data Available
I have a scenario that user can ask for certain data say income & expense for last 3 months on monthly basis. I have wrote following query which works well but it has an issue: Let say I have asked for data of FEB 2007, JAN 2007, DEC 2006 using the following query it shows me correct result when all three months have some data but it doesn't show me the desired result when you don't have data in one or more months. Any suggestion to get result when there is no data:
Truncated Time Values Using TIMEDIFF With ORDER BY Query
I'm using the following query: SELECT glider, timestamp, TIMEDIFF(timestamp, UTC_TIMESTAMP()) AS last_contact FROM surfacings INNER JOIN (SELECT MAX(timestamp) AS most_recent FROM surfacings GROUP BY glider) AS tmp WHERE surfacings.timestamp = tmp.most_recent; to calculate the amount of time that has elapsed since the last inserted timestamp for each glider. Everything works fine: ....
Next And Prev DisplayOrder Values (was "Help With MySQL Query")
I have a table 'Album' containing columns 'PageID' and 'DisplayOrder' (both INT). On each page that is displayed I want to have 'previous' and 'next' links to other pages, which are arranged via the DisplayOrder column. At the moment I'm doing this in 2 separate queries: SELECT DisplayOrder FROM Album WHERE PageID = $currentPage $currentNo = [result of last query] SELECT PageID FROM Album WHERE DisplayOrder IN ($currentNo-1, $currentNo+1) Highly simplified but you get the idea. Would it be possible to combine this into one query?
How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy! I can make a simple single query using MYSQL Query Browser, say: qry1: SELECT ID, Area FROM data GROUP BY Area How can I store this as a query inside MYSQL, rather than having to code it each time? In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query: qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE How can I store this as a query and then pass the variable from code? In Microsoft Access I could base a query on the results of another query, so following example above: qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area; How can I store this as a query in MYSQL.
Get All Values
Is it possible to just return all the values that are in a column?
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'
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
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?
Top 2 Values
I have a table called Labs and a table called EE's. Each EE has many labs in the lab table. I am trying to figure out how I find the top 2 labs (based on date) and return that along with all of the fields in the EE table. Would I use a sub query? Something like this? SELECT * FROM ee, WHERE ee.id in (select top 2 from labs). This didn't seem to work.
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.
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?
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?
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
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
Total Sum Of Values?
this isn't a question of "how do I do this" but more of a "what's the best efficient way of doing this" the current code is: $result=mysql_query("SELECT votes FROM list"); votes is always a numerical value what's the most efficient way to add the total value of the votes?
Trigger Old And New Values
I have two tables with some fields. RealTableName ID int FieldOne varchar FieldTwo varchar OldValuesTableName ID RealTableNameID FieldOne FieldTwo I want to create a trigger which transfers the current values of the `RealTableName` record I am updating to the `OldValuesTableName`. This should happen before update.
Returning All Values
I'm sure this is a straight forward and obvious one: I have this: SELECT * FROM tbl_contacts WHERE con_Customer = "#URL.con_Customer#" Which obviously when you pass a value over the url returns the relevant records. How can I pass a value to the url which passes ALL the records back. Is it something like ?con_customer='%'?
Get The Enum Values
how can I get the variable that enum field can take it? like if I have Sex field that could take (male,female) . I need something like that: select (all the values for the sex field) ; //output (male,female).
How Do I Get Values From Select?
I need to assign values pulled by a select query to varibles, but the only thing I know how to do with the values I get from a select query is make a table. Below is the code that puts the values I need stored in variables in a table: $query = "SELECT title, description, height, width FROM images WHERE filename = '$imagepath'"; $result = mysql_query($query) or die (mysql_error()); echo "<TABLE BORDER=1>"; WHILE ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo "<tr>"; WHILE ( list ($key, $value) = each($row) ) { echo "<TD>" . $value . "</TD>"; } echo "</TR>"; } echo "</TABLE>"; Its designed to call only one row so I just need the four values called in the query to have their own variable,
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.
Ordering Values
How do i order numbers retrieved by there value, such as: 1,2,3,4,5...12,13...22 so its like that instead of 1,11,12,13,14,2,21,22,23,24,31,4,5,6...
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?
Between Values Of 2 Columns
Consider the following table: FROM_HERE-----TO_HERE-----VALUE 1--------------------10--------------------a 11--------------------20--------------------b 21--------------------30--------------------c How could I get the VALUE(a,b or c) of a $number that is between FROM_HERE and TO_HERE? For example $number = 17; 17 is between 11 and 20, so VALUE = b. (Note that 11 and 20 must be included, so if we have $number = 11 or $number = 20 -> VALUE must be "b")
Retrieving Values From Url
i want to put the value in a variable but dont know how to retrieve it: i want to use record_num = 3 as a variable: URL: .../review.php?,record_num=3 EXAMPLE OF VAR $link_id ='[record_num]'; SOURCE <a href='review.php?,record_num={$row['link_id']}'>LINK</a>
Values NOT In Range
I want to find all values for a field NOT in a range. In this case, I want to see which membersIDs are NOT used say between 1000-1000. Finding the used ones is dead easy, but I can't find a simple way (besides creating a list of all numbers between 1000 and 10000) to get the ones that are NOT used.
Grouping Only Top 10 Values
I am trying to figure out how to run something complex like this: my table structure is similar to this: player_id player_points each player has alot of rows with points. The way we have our listings displayed is by doing a SELECT player_id, SUM(player_points) as total GROUP BY player_id ORDER BY total DESC. This works great but now we need to change our queries to reflect only top 10 scores of each player. So if a player has 50 entries for different points, we need to grab only the top 10 points and find the sum of that. Our table has over 20 million rows. Can anyone guide me on how I would go on doing this? One way is by passing row by row into php and counting only top 10 rows. But this means going through all 20 million rows through php, very ineffective.
Adding Values?
How can i add the values of different rows in mysql using php? ie: id field! 1 3 2 2 3 4 4 2 5 6 6 20 7 7 8 5 n 1 ----------------- total = value of id#1 upto id# n. for this example total should be = 50
Row Values COUNT
I'm creating database that has three rows Month, dtate and values. There I want to get count all values in some month rane. then I used query like this... SELECT month, values ,COUNT(*) FROM table_test where month between month1 and month3 group by month That query result was How many row in the that month period. But I want to count the values in the that row( values row).
Round Values
How do I round values in a view. Do I change the table? The type that needs rounding is currently formatted as double.
How To Get Different Values From The Column
I am reposting my question here as there's no reply from the other post (different subject). In the column, there are mixture of multiple same and different values. How do I write to obtain all different values with the limited row? For example, select pet from pet_customers where ?????? order by Date Desc limit 25;
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'.
Add Values Into A Column
I have a column called year with the format YYYY-MM-DD, but I would like to have year, mount and day in three different columns in my database. How can I do this?
Get Min & Max Values Of A String
I have a field 'zip' in a table in my database that records values in the form on 'something/023/something', 'anything/346/somemore','kztt/1984/asdg' I want to get the min and max values of this field in particular the numbers in the string. Initially, I simply run a query to get the min and max value of this field which works until the numbers in the string went to 4-digits instead of the usual 3. Example query: select min(zip) as min, max(zip) as max from $dbtable Then I run an explode function to retrieve the numbers in the string. This query now return values like min=999, max=1000 instead, when the min number should be 023 and max is 1984
Modifying Row ID Values
I want to be able to delete a row in a table, and then have every row's ID from below that row to subtract one, in order to move up. Here's how I have it, and I'm sure I'm doing something wrong. $query = "DELETE FROM tbl_image WHERE id = '{$_GET['del']}'"; mysql_query($query) or die('Error : ' . mysql_error()); $query1 = "SELECT FROM tbl_image WHERE id > '{$_GET['del']}"; mysql_query($query1) or die('Error : ' . mysql_error()); $result2 = mysql_query($query1); while($row = mysql_fetch_array($result2)) { $id = $id - 1; $query2 = "UPDATE tbl_image SET id = '$id'" mysql_query($query2) or die('Error : ' . mysql_error()); }
Return Values
SELECT someNumber, COUNT(someSubject) FROM someTable WHERE (someNumber BETWEEN 10 AND 20) GROUP BY someNumber The query returns the count for someSubject, grouped by someNumber providing at least one matching someSubject was found for each someNumber. Where there were no matching someSubjects, the someNumber group is not returned. Specifically, I am getting back the count for 16, 17, 18, 19 and 20. Although the count for 10, 11, 12, 13 and 14 is zero, I still want this to display in the returned table. Can any help to show me how I can force the BETWEEN values to display when the count for that value is zero?
|
TRACKER
