How To Code Sql Update Using Aggregate Function
These 2 alternative queries express what I want clearly:
update phpList_work p, order_info o
set p.oid=Max(o.oid)
where p.cid=o.cid
UPDATE phpList_work p LEFT JOIN order_info o USING (cid)
SET p.oid=max(o.oid)
they get the error
#1111 - Invalid use of group function
phpList_work is a list of Customer records.
phpList_work already has a subset of the customer list, with ID values (cid)
order_info is the order master, keyed by its id (oid) and has cid
The invalid query shows I want to select the order_info value of oid that is the MAX(oid) for each value of cid in phpList_work.
The manual does not discuss this. If the functions do not support this obvious and useful syntax, how do you do it?
View Complete Forum Thread with Replies
Related Forum Messages:
IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere! The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over £300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....
View Replies !
Aggregate Function
I am kind of confuse how to select this data Which city has the most airports. There are two tables airport and cities the airport has two column IATA_CODE and the name of airports where as city has the IATA_CODE and the name of the cities. I used two diffrent ways but I dont't which one is right. select name, count(distinct IATA_CODE) from airports group by name; SELECT c.name,count(*) AS no_of_airports FROM airports a LEFT JOIN cities c ON c.IATA_CODE = a.CTY_IATA_CODE GROUP BY a.CTY_IATA_CODE; both have the same result and I coudn't be able to select once city who has the most airports.
View Replies !
Need For Aggregate Function First?
I have a table, like so: col1 col2 A 1 A 2 B 4 B 4 and I want to group it on the first col, and for the second column I want to test if all grouped values or the same, if they are I return the value, else i return some default eg. 0 My query should be something like this: select col1, if (count(distinct col2) = 1, first(col2), 0) as col2 from table group by col1 -> the aggregation function first does not exist..but I need it, with what can I replace it?
View Replies !
Aggregate Function (AVG) Query Q.
I've been looking at the AVG function, and am trying to figure out how to return a very specific, complex value using the function. Query: sqlTESTavg_curr = "SELECT AVG(DISTINCT TESTcomp) FROM TEST2005 WHERE (StatusCluster = 'Freshman') AND ((Class_Num = 2)" I am trying to et the average of current freshmen applicants TEST scores. The problem is, my table is designed to capture a record for every single student every single day. I can specify to look for only records with dates of "today" - but if I try to use the sql above- it's not going to be correct because I have multiple records with identical scores which will skew the average. We have student IDs that are the table- what I would like to do is set the query to return distinct by the Student ID's but actually return the average of the TESTcomp fields. so - something like this: sqlTESTavg_curr = "SELECT DISTINCT STU_ID AVG(TESTcomp) FROM TEST2005 WHERE (StatusCluster = 'Freshman') AND ((Class_Num = 2)" Obviously that doesn't work. Is this just too complex of a query? How should I go about getting the average of a field on the values from a distinct field that is not the same?
View Replies !
VB Code To Update MySQL Table
I'm using mysql with vb 6.0. When i update mysql table from my application, i'm getting an error as below, Runtime error. ' -2147467259(80004005): [MySQL][ODBC 3.51 Driver][mysqlid-5.0.45 community-nt]Build where -> insert_fields() failed. This error comes whenever "rs.update" executes.
View Replies !
Update Returns Wrong Return Code
I have an application that works perfect with SQL SERVER. Now I am using MySQL 5.0 withODBC 3.51 (and I tried 5.1). The problen is that I issue a update to a table that has no records at all and I get a return code 0 (SQL_Success) per the ODBC trace in Windows 2003 server OS. (On the SQL SERVER I get return code 100 - NO DATA) Why do I get a return of 0 when there is no record matching the WHERE (actually no records at all? My code is looking for a return code to tell me NO DATA so I can then do an INSERT.
View Replies !
UPDATE Function Very Slow??
I have a large table (77,321 Rows) and I'm trying to update it. For some reason, UPDATE takes a long time. Maybe it's my query? I'm doing it through php, maybe that's it? I'm running the program locally using the CLI, and it still takes ages. Should I not expect it to be as fast as SELECT functions? $update ="UPDATE `ch_products` SET `products_weight` = '".$IDS[$i][Weight]."' WHERE `xref1` = '".$IDS[$i][ID]."'"; How can I make this faster?
View Replies !
Aggregate AVG
I have a MySQL call:PHP Code: $sql = mysql_query("SELECT COUNT(id) AS count, AVG(rating_avg) AS avg, SUM(views) AS views FROM articles WHERE author= '".$this->author."' AND status = '10' GROUP BY author"); As you can see I have three aggregate functions in the statement. The problem comes in with getting the average rating -- AVG(rating_avg)The call is looking at several articles written by authors. If an article has not been rated by any visitors, it has a rating of 0.00.Is it possible to setup a mysql statement that gets all three aggregate functions but leaves out a rating of 0 in the average function?
View Replies !
Aggregate SQL
I have a table as follows, representing a series of games for a sport. +--------+---------+---------+--------+ | gameID | team1id | team2id | winner | +--------+---------+---------+--------+ | 2502 | 22 | 12 | 12 | | 2503 | 21 | 13 | 21 | | 2515 | 13 | 11 | 11 | | 2516 | 22 | 14 | 14 | Each game stores the game's winner as the winning team's id in the winner column. I'd like to produce a table with each team's id, followed by the number of wins and losses, like so: teamid wins losses ---------------------- 1234 | 3 | 2 4321 | 1 | 0
View Replies !
Aggregate Time Sum
I have many "track length" fields in a table, I wish to have these all added up and produced as a field... Much like "SELECT SUM(`x`) FROM `table` WHERE 1" I see there is an ADDTIME but... This adds one value to another, I just wish to add all the fields together
View Replies !
Aggregate Functions
I was having to atmysqlting to calculate the monthly average of an occurrence over a 4-year time span. Here's the basic query: SELECT t1.county,t2.year, t2.month, count(*) as total FROM t1 LEFT JOIN t2 on t1.year = t2.year AND t1.month = t2.month GROUP BY t1.county, t2.year, t2.month The output I get looks sort of like this: county | year | month | total 01015 | 1999 | 10 | 2 01015 | 1999 | 11 | 3 01015 | 2000 | 2 | 5 So here the t1.county variable contains over 500 unique items and I'd like to list a monthly summary for each one, including the months which are not contained in t1.month. I created a separate table (t2) with all the possible months and years and thought that a LEFT JOIN would do the trick. But, maybe because the t2 table doesn't contain a county variable with each combination of year and month it doesn't work right?
View Replies !
Aggregate Keys
CREATE TABLE `tbl_vel_product` ( `family_id` varchar(25) NOT NULL default '', `catagory_id` varchar(25) NOT NULL default '', `colour_id` varchar(25) NOT NULL default '', `price` int(11) NOT NULL, `qty_in_stock` int(25) NOT NULL, PRIMARY KEY (`family_id`,`catagory_id`,`colour_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Where my primary key is several foreign keys from other tables. I have a webform that returns this product key and I wish to search for the product with that key.. How do I construct a query of the form.. SELECT * from tbl products WHERE [family_id, catagory_id, colour_id] = [URL_PARAMETER]...?
View Replies !
Aggregate Functions + ORDER BY
I want to find the sum of some rows, which I can do fine. Then, I want to order by that sum. Is this possible? I know ms access does it the following way, which I've read will work with mysql also: SELECT nav_result_id, SUM(timingpoints) AS TimingPoints FROM leg_results GROUP BY nav_result_id ORDER BY SUM(timingpoints) However, I get this error message: "#HY000Invalid Use of Group Function" Can anyone tell me why, and possibly give me a hint how to correct my syntax to prevent this, and make it work
View Replies !
Double Aggregate SUM And COUNT
I am trying to calculate the total amount to charge per customer. I have a customers table and an orders table and a devices table. Basically it works like this: Get the price per unit (price_per_unit_per_order) out of the orders table. Get the total number of devices in the current order (total_devices_per_order). SUM(total_devices_per_order * price_per_unit_per_order) AS total_per_customer while the customer is the same. Basically there can be multiple orders for the same customer each with different order prices, but I only want one total number per customer. Here is what I have: SELECT SUM(COUNT(o.order_id) * o.price_per_unit) AS total_per_customer FROM customers c, orders o, devices d WHERE c.customer_id = o.customer_id AND o.order_id = d.order_id AND d.active = 1 GROUP BY c.customer_id, o.order_id ORDER BY c.customer_id, o.order_id ASC
View Replies !
Mysql 5.0 & ASP/ASP.Net Aggregate SUM Problem
I'm running into a brick wall when attempting to run querys in asp/asp.net with sum() being used. The issue I'm having is any query containing sum() will no display and breaks the asp code in either asp/asp.net(vb) I am using 3.51 odbc driver dsn-less I have even attempted to cast the sum into a decimal still encountering the issue. Anyone have any ideas as to what 5.0 changed because previously I have no issues with Sum() in my asp/asp.net sql queries.
View Replies !
Aggregate Functions In Nested Set
I'm trying to make work a simple query that extracts from the db all the categories that contains at least one product and show the total number of products contained in each node, ignoring invisible categories (and products inside them). This is the query: ....
View Replies !
Adding Aggregate Functions With Columns
Does anyone know how to add the values returned from aggregate functions to the values in the columns. I have to compute the average price of a product given from a list of products which have their own prices, and display the products information with its price, the average price including that product, and the average price not including that product. so far it got this :----: select (select avg(pprice) from vendor) , (select max(pprice) from vendor), (select min(pprice) from vendor), avg(pprice) , vname from vendor a group by vname; which calculates the average but then i got lost.....
View Replies !
Aggregate 15 Minute Data Into Hourly
- Running MySQL 5.0.51a on FreeBSD - I have data like this in my database: +---------------------+----------+ | datetime | NUMBERS | +---------------------+----------+ | 2008-06-06 12:15:00 | 23 | | 2008-06-06 12:30:00 | 9 | | 2008-06-06 12:45:00 | 47 | | 2008-06-06 13:00:00 | 62 | | 2008-06-06 13:15:00 | 78 | | 2008-06-06 13:30:00 | 68 | | 2008-06-06 13:45:00 | 47 | | 2008-06-06 14:00:00 | 56 | +---------------------+----------+ Which I need to be aggregated to hourly data - like this: +---------------------+----------+ | datetime | NUMBERS | +---------------------+----------+ | 2008-06-06 13:00:00 | 141 | | 2008-06-06 14:00:00 | 249 | +---------------------+----------+
View Replies !
Hide Aggregate Columns For A Subquery?
I'm trying to form a query that will use a aggregate function in a subquery, but it isn't the aggregate function that I want to use, but another column. Here's the code: SELECT * FROM member_data WHERE id = ( SELECT member_id, SUM(active) AS listnum FROM email_lists GROUP BY member_id HAVING listnum = 0 ); This code won't work, because there are two columns in the subquery, the one I need for the WHERE clause is member_id, but I need the SUM column to pick out the right data. Is there any way of hiding the SUM column so it can be used by the HAVING clause, but only produces a result set with one column?
View Replies !
Sub Query - Aggregate Fields Based On Min N Max
In the Users table below there are duplicate users by email address +---------------------+------------------------------+-----------------+-----------+ | ts | email | field1 | field2 | +---------------------+------------------------------+-----------------+-----------+ | 2009-01-31 06:51:14 | user1@rediffmail.com | 05 | 03 | 2009-01-31 16:07:39 | user2@yahoo.com | 02 | 02 | 2009-01-31 16:15:02 | user2@yahoo.com | 09 | 04 | 2009-01-31 16:16:00 | user2@yahoo.com | 06 | 08 | 2009-01-31 16:19:52 | user2@yahoo.com | 01 | 09 | 2009-01-31 02:04:36 | user3@rediffmail.com | null | 01 | 2009-01-31 02:12:34 | user3@rediffmail.com | 01 | 03 | 2009-01-31 02:20:31 | user3@rediffmail.com | 08 | null +---------------------+-----------------------------+--------------+-----------+ I want to fetch one record per user ‘user1,field1,field2’ For user 1 select field1 where min(ts) select field2 where max(ts) the final output should be user1,05,03 user2, 02, 09 user3, 01,03 (max of ‘field2’ is null so it should pick the field value which matches the next min ‘ts’ val)
View Replies !
Show Rows With Result 0 For Aggregate Functions
have a query like this: SELECT C.idc, C.name, count(CM.idm) from C, CM where C.IDC=CM.IDC and CM.idci is null and C.type='class' GROUP BY C.IDC The result table only contains the elements of table C where the count() is >0. How can I obtain a result table that contains all elements of table C with their count (either 0 or >0) ?
View Replies !
Where Clause Issues When Selecting Between Dates With Aggregate Functions
I am working on a reporting tool that scrapes ebay feedback scores and enteres then into a database to be used later in various reports. One report I am trying to create will display how much a user's feedback score has increased over a specific period of time (e.g. last 30 days). There are two tables being used in this query. One simply lists the the identities being tracked (identities) and the other records their feedback score (feedback). Each day a new row is created with each identity's current feedback score. When I try to get the total increase in feedback score since we started recording data it works just fine: SELECT i.name , MAX(f.feedback) - MIN(f.feedback) AS calc_feedback FROM identities AS i LEFT JOIN feedback AS f ON i.id = f.identities_id GROUP BY f.identities_id ORDER BY calc_feedback DESC , i.name ASC But when I try to add a WHERE clause that specifies a specific time period to pull this data from it doesn't return any rows. It doesn't give me an error, it just doesn't return any rows. Here is the query with the WHERE clause I am using (I've tried various other one similar to this one). SELECT i.name , MAX(f.feedback) - MIN(f.feedback) AS calc_feedback FROM identities AS i LEFT JOIN feedback AS f ON i.id = f.identities_id WHERE f.feedback BETWEEN NOW() AND NOW() - INTERVAL 2 DAY GROUP BY f.identities_id ORDER BY calc_feedback DESC , i.name ASC
View Replies !
Php Code
I have some fields that contain both text and some php code which is activated using the eval command. I need to search and replace this php code with nothing, I want to get rid of it from mysql fields, however, because I am submiting an item of php within this sql command it seems to be getting upset... [code] update `articles` set `content`='[php]<?php include('./1.php'); ?>' (`content`,' ');
View Replies !
Zip Code
Here is what I am trying to do. Maybe it's not possible, but I have a database for employees that I have created. It includes a name, photo, title and the zip code. Without bothering with selecting a state, I want to be able to type in a five digit number and if the number they search with is 200 or 300 numbers less than or greater than a zip code I have specified for an employee it retrieves the data.
View Replies !
SP Code
I am a SQL Server user and I tried to get a stored procedure to work with several iterations. Here is what i tried first: delimiter // g create procedure P() begin select * from ips_transactions; end // g This is what it returned: Failed to execute SQL : SQL delimiter // g create procedure P() begin select * from ips_transactions; end // g failed : Unknown command ''. Then I tried: delimiter // create procedure P() begin select * from ips_transactions; end // And got this, which I thought my be a confirmation that the procedure was created: Output from SQL command delimiter // create procedure P() begin select * from ips_transactions; end // .. No data returned I tried to call it call P() and got this: Failed to execute SQL : SQL call P() failed : PROCEDURE dev-demoasp3.P does not exist What am I doing wrong? I have a client that wants all of my SQL server SP moved over, but I cannot even do a slect statement much less create temp tables and the such
View Replies !
Change The Code
when i put data in my mysql table on mine local webserver it appears only when i change the html code of the page that contains the php code. I already put the meta-tag 'no-chache' on mine site but it doesn't seams to work
View Replies !
Code Generators
Is there a FREE code generator program that will generate forms and listings and maybe a complete set of updates and edits forms?
View Replies !
Error Code 28
mySQL started to return error code 28 whenever I try to insert any data, create tables, etc. I used 'perror' on 28 and it returned: Code: No space left on device I then used 'dd' on the drive and it said that all partitions have at least 20% space left on it. What can I do to fix this?
View Replies !
Error Code 28
I'm getting this error all the time, among others.. So I guess that I had run out of diskspace, so I did a "SHOW VARIABLES" to see where my data was stored, and found them to be: bdb_home /var/db/mysql/ bdb_tmpdir /var/tmp/ datadir /var/db/mysql/ so, next was to do a df to see if /var was all down.. heres my result: /dev/ad0s1e 257998 78556 158804 33% /var I can't seems to find out what is wrong here.. From what I can see the shoulb be 33% space left on this device..? How come MySQL gives me a error saying no space left?
View Replies !
One Line Of Code
First here is the line of code Im getting an error on... $q2 = MYSQL_QUERY("SELECT *, DATE_FORMAT(Date, '%M-%D-%Y') AS newdate FROM usercars WHERE User = '$user' AND Active = "T"") or die ("SQL Error"); Before I tried to add the Date_format the lines worked fine and read as follows. $q2 = MYSQL_QUERY("SELECT* FROM usercars WHERE User = '$user' AND Active = "T"") or die ("SQL Error"); Second, when working will this force the date to be 02-24-2005 or 02-24-05 ? I would prefer the second option, any idea how to force that format?
View Replies !
Need Help Revising Some Old Code
Alright, I found this code in a much older post: PHP Code: $sql = mysql_query ( 'SELECT FOUND_ROWS() FROM table_name' ); $max = mysql_result ( $sql, 0 ); $sql = mysql_query ( 'SELECT * FROM table_name ORDER BY id ASC LIMIT ' . ( $max - $get ) . ', ' . $get ); while ( $t = mysql_fetch_assoc ( $sql ) ) { echo $t['id'] . '<br />'; } It's basically supposed to the newest 7 entries in a table in descending order for mysql versions that don't support sub-queries, like mine doesn't. I was wondering if someone could get this to work properly. First off, I'm not sure what value $get is supposed to have, because it was never made, and I'm also unsure why $max is set to 0.
View Replies !
Zip Code Column
I am capturing zip codes in my database. In the table, I have the type set to "int(5)". I am running into problems where a given zip code starts with a "0" (ex. 06539) in that the leading "0" is being stripped.I've got a workaround in place now but I'm wondering what the "right" way to fix this would be. Should I change the type of the zip code field to something like varchar?
View Replies !
SIC Code List
I'm working on an app that needs to reference SIC codes. They basically map numbers to industry categories, making it easy to categorize businesses. I've spent plenty of time googling, and have only come up with SIC lookups, not full lists. The one list I did find only went out to 6 digits, and I need a list containing 8-digit codes. Does anybody here have a list of SIC codes in either a mysql database or csv file? Or have any idea where I can find one?
View Replies !
Zip Code Locator
Let's see the brain childs work this one out! I have a table that has a whole bunch of customers in there. One field that I am interested in is customer_zipcode. With that all of our customers have their zipcode listed.Now, what if I want our managers to be able to put the store zip code in a box and then they define how many miles they want to travel such as 25 miles (hard code if you need to make it easier is fine). Then what I would like to have returned is something similar to: customer_id | customer_fname | customer_lname How would I go about doing that? I figured that I would have to use a LEFT JOIN customers ON customers.zipcode = zip_code.zip_code .... but the rest I am kinda clueless on.
View Replies !
Foreign Key Code
I'm trying to reference the two primary keys in the "choise" table in "clicklist" but just can't get it to work. I have tried one of them up to this point, but it doesn't work either. :( CREATE TABLE `Choice` ( `productID` int(11) NOT NULL, `Nickname` varchar(32) NOT NULL, `Quantity` int(11) NOT NULL, `TimeOfSelection` datetime NOT NULL, PRIMARY KEY (`productID`,`Nickname`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; create table `clicklist` ( `Nick` varchar(32) not null, `productID int(11) not null default '0', key `productID` (`productID`), constraint `productID` foreign key (`productID`) references `Choice` (`productID`)) engine = InnoDB default charset = latin1; ERROR (HY): Can't create table '.perlacasa/Clicklist.frm' (errno: 150)
View Replies !
MySQL Code
I am scripting In ASP and with a DSN connection to mysql. I need a script which can query the database to see if two fields (username or email address) which are both submitted by a user exist anywhere in the database.I have tried various scripts so far, but none seem to work. PATH> User submits (joebloggs, joebloggs@hissite.com) > Check DB to see if either of these exist anywhere in the file. Connection String : 'Init the DB Connection on error resume next dim adoConnstep dim adoRSstep dim strQuerystep set adoConnstep = Server.CreateObject("ADODB.Connection") adoConnstep.Open "DSN=mydsn"
View Replies !
Zip Code Query
Ok when using this I have it so even planners can find racers. The Racers opt to be searched and how far they are willing to travel to race amoung other things. When an event planner searches for Potential Racers he type in her zip code and I want it to find all racers that are willing to travel to his zip code. What I thought of for a solution is when the racer makes herself searchable to populate a TEXT Column with all the Zips that are in her radius. The problem with this is that there could be around 900 zip codes thats around 5,400 charaters. To me that seems like a lot. Is this still the best solution and if so when I get a list of all the zip codes in her radius how can I combine all the Rows to a single row so I can enter in into a single field.
View Replies !
|
TRACKER
