Invalid Use Of Group Function (was "Baffled By Query Error")
trying to figure out why I keep getting this error with the following query:
SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins
FROM account a LEFT JOIN calls c ON c.account_id = a.id
WHERE c.calldate >= DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2
AND SUM(c.agent_product_time) >= '500' GROUP BY c.account_id ORDER BY mins
ERROR: #1111 - Invalid use of group function
View Complete Forum Thread with Replies
Related Forum Messages:
Error:invalid Use Of Group Function
version:mysql 4.0.18 for win hi,all dear:) when execute sql-statement, error raise: update customer,cu_order set customer_point=0 where customer_keyid=order_customerid and DATE_SUB(now(),INTERVAL 1 YEAR)=DATE_FORMAT(max(order_time),"%Y-%m-%d 23:59:59") error:invalid use of group function i beg someone tells me why?how resolve? thx
View Replies !
Invalid Use Of Group Function & SUM() And MAX()
I have the following Sql Statement... I'd like to find the sum of hrs worked by an employee in the month, the required number of hrs, and the sum of the difference between the two, the worked hrs are calculated by subtracting the max vtranstime - min vtranstime and the required hrs are calculated by subtracting max to1 - max from1....
View Replies !
Invalid Use Of Group Function - Max(`mainid`)+1
I am trying to insert a incremented number into the menu table. I will have an autoincremented number as well as the mainid number. When I tested the code that is below, the above error happened. Can someone please tell me the correct way of doing this? I am getting really frustrated with this. Nothing wants to work for me. require "config.php"; $insert06 = "INSERT INTO `menu` (`mainid`) VALUES (max(`mainid`)+1)"; // error line if (mysql_query ($insert06)) { print "Mainid added."; $query2 = mysql_query("SELECT mainid FROM menu") or die ("Could not query because: ".mysql_error()); while($row2 = mysql_fetch_array($query2)) { echo $row2['mainid']." = Mainid"; } } else { print "<p>Could not add the entry because: <b>" . mysql_error() . "</b>. The query was $insert06.</p>"; } mysql_close();
View Replies !
Baffled By A Query Request
I got a task in my database design class which has baffled me. No matter what I'm trying to to, it's a no go. CREATE TABLE IF NOT EXISTS `country` ( `Code` char(3) NOT NULL default '', `Name` char(52) NOT NULL default '', `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL default 'Asia', `Region` char(26) NOT NULL default '', `SurfaceArea` float(10,2) NOT NULL default Ɔ.00', `IndepYear` smallint(6) default NULL, `Population` int(11) NOT NULL default Ɔ', `LifeExpectancy` float(3,1) default NULL, `GNP` float(10,2) default NULL, `GNPOld` float(10,2) default NULL, `LocalName` char(45) NOT NULL default '', `GovernmentForm` char(45) NOT NULL default '', `HeadOfState` char(60) default NULL, `Capital` int(11) default NULL, `Code2` char(2) NOT NULL default '', PRIMARY KEY (`Code`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; I am asked to display the highest populated country on each populated continent. And it is supposed to be done in one query.
View Replies !
Invalid Use Of Group By
I have the following query: SELECT CLAIMPOLICYNUMBER AS Plan, PROVIDERSPECIALTY AS Specialty, TINNUM AS Tin, SUBTINSEQNUM AS SibTin, CHARGETYPECATEGORY AS ChargeCategory, SUM(BILLEDCHARGE) AS Billed, SUM(PAID) AS Paid, " + _ COUNT(DOCUMENTNUM) AS ChgCount FROM BAT WHERE CASENUM='0000911' AND CHARGETYPE <> 'DENTAL' AND CHARGETYPE <> 'VISION' AND CHARGETYPE <> 'ADMIN' AND PMTDATE >= '2005-06-01' AND PMTDATE <= '2005-06-30' GROUP BY CLAIMPOLICYNUMBER, PROVIDERSPECIALTY, TINNUM, SUBTINSEQNUM, CHARGETYPECATEGORY ORDER BY SUM(BILLEDCHARGE) It works great without the ORDER BY clause, but with it, I get the aforementioned error.
View Replies !
Group By Returns Invalid Dates
Hi i have one table: ID, User, Score, Date 1, mac, 1234, 2007-12-09 09:01:07 2,kurca, 3232, 2007-12-05 09:43:57 3, mac, 999999, 2007-11-07 09:44:14 I wan to create a high score list which will represent the best scores but not repeating anyone for example: 1, mac, 999999, 2007-11-07 09:44:14 2,kurca, 3232, 2007-12-05 09:43:57 Now i use: SELECT ID, User,MAX(Score) as maxScore,Date FROM `table` GROUP BY User ORDER BY maxScore DESC with this query the score is right but the date is invalid example: 1, mac, 999999,2007-12-09 09:01:07 2,kurca, 3232, 2007-12-05 09:43:57
View Replies !
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 !
Mysqlhotcopy Error :: Invalid Db.table Name
I have a large database that I'm unable to backup with mysqldump. So I tried mysqlhotcopy , testing it on a smaller wordpress installation as: mysqlhotcopy bestfood_word /path/to/new_directory But it gave me this error: Invalid db.table name 'bestfood_word.bestfood_word`.`wp_categories' at /usr/bin/mysqlhotcopy line 854. Could anyone tell me what would cause this and if I should use a different command?
View Replies !
User-defined Function: ERROR 2013 (HY000): Lost Connection To MySQL Server During Query
With mysql-4.1.1-alpha, using any User Defined Function ( UDF) at the query execution stage, on Linux 2.6, gave me the following error: "ERROR 2013 (HY000): Lost connection to MySQL server during query" However, upgrading to mysql-4.1.2-alpha seemed to solve the problem. Both versions were configured exactly as follows ../configure --with-openssl --enable-thread-safe-client --with-mysqld-ldflags =-rdynamic The only way I could solve this was by upgrading to 4.1.2-alpha-log. Then, recompiling the functions, drop the old UDF functions, then, recreate. So far so good. Anyway, if you run into this problem, or just want more detail on the sample User Defined Functions that come with MySQL ( /sql/udf_example.cc), I've put my notes in the following link ( TIP 27 )
View Replies !
Table Join Invalid Query
I have this table join below and I am getting an invalid query. How can I put the 15th line ($sql .= " group.id = master.id "; and the 17th line ($sql .= " where ".$currentrow_sql ; together. Is it even possible to do that?
View Replies !
Count Function & Group By
Getting an error saying I cannot Group on JState2 Is this a version issue? What is an alternate? (want to show only results having greater than 2 of the same JState 's) SELECT COUNT(JState) as JState2, JID, StateTerm FROM JPosts2, State WHERE State.StateAbbr=JPosts2.JState AND (`JClass` = 'Mechanics') GROUP BY JState2 HAVING JState2 >4 ORDER BY JState
View Replies !
GROUP BY Function And Getting Rid Of NULL Results
Ok, here is a query I'm doing: SELECT site , sum(uniques ), sum(trials +full_price ), round(sum(uniques)/sum(trials +full_price )) as ratio FROM mpa3_all_stats WHERE date BETWEEN '2005-12-01' AND '2005-12-15' GROUP BY site What I want to do is get rid of the null results that I'm getting from this return. As if I order by the last field, the nulls will go to the top if it's ASC. Which I need to do a ASC order on the last column. But I can't figure out a while to get rid of the null results. I've searched everything I could think it would be under. Here is partial data I'm getting: Code: +------+----------------+----------------------------+------------------------------------------------------+ | site | sum( uniques ) | sum( trials + full_price ) | round( sum( uniques ) / sum( trials + full_price ) ) | +------+----------------+----------------------------+------------------------------------------------------+ | 18 | 99865 | 119 | 839 | | 19 | 121386 | 188 | 646 | | 20 | 479 | 0 | NULL | | 21 | 1314 | 0 | NULL | | 22 | 100541 | 226 | 445 | | 33 | 21457 | 169 | 127 | +------+----------------+----------------------------+------------------------------------------------------+
View Replies !
Function Error
I´m trying to create a function using SQL language however even the command below that I found in the MySql docs is not working, I always get the same error whe I try to create a function Any suggestions ???? CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello, ',s,'!'); Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(s Char(20)) Returns Char(50) RETURN CONCAT('HEllo,',s,'!')' at line 1 (State:37000, Native Code: 428)
View Replies !
Error Messages In Function
First, I cannot use PHP or C. My requirements are for a pure MySQL interface. I am using MySQL v5.0.Is there any way to output a message to the console from a FUNCTION?This is needed mostly for error handling, but all error handling functionality seems to only point to PHP or C, which I cannot use. In postgresql this is easily done with RAISE NOTICE 'msg'; Like in Ingres it can be done with WRITE 'msg'; But I am baffled that MySQL does not have any equivalent functionality.
View Replies !
Function For ERROR And/or ERRORNO?
This may seem like a silly question, but MySQL has the mysql_error & mysql_errno API functions. So why is there no similar ERROR & ERRNO that can be called from the command line? Similarly, MS SQL Server, PostgreSQL, INGRES, etc. all have a RAISE ERROR and/or RAISE NOTICE function to output messages to the user. Why is there no similar functionality in MySQL? The above seems like pretty basic functionality that was overlooked. Finally, I searched throughout the MySQL site, mailing lists and forums, but I can find no way to formally request these enhancements. Does anyone know of how that is accomplished?
View Replies !
The Function 'IN' Causes Error In MySQL 4.1.9
I am currently using MySQL 4.1.9 and I'm planning to upgrade to MySQL 5.0 soon. But for the mean time, I am experiencing problems with the function 'IN'. Here's the error message I received.. #1270 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation ' IN '
View Replies !
SQL Error - GROUP BY
I'm using the following to retrieve data: Code: select * from items where ItemCategory = $CategoryID GROUP BY country when i call the page using the following format: http://www.mysite.com/index.php?CategoryID=45 everything works fine as i intend it to. However, if i call the page leaving out the 'CategoryID=45', ie http://www.mysite.com/index.php it brings up the following error: Quote: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY country' at line 1.
View Replies !
Replace Function :: Error 1064
I am trying to do an update/replace function in MySQL but I am getting MySQL error #1064. Basically the query is just trying to update a field called Description in a table called Widgets by changing some values to a new value. So, for example, I want to change the field 'oldvalue123' to 'newvalue123' The query works well in MS SQL 2000 but not in MySQL- Here is the query Update Widgets Set Description = (Select Replace(Description,'oldvalue','newvalue')) From Widgets
View Replies !
Error: Call To Undefined Function Mysql_connect() .....
I'm running MySQL 4.1 with PHP 5.0.2 on Windows 2000. However I cannot connect to the database. I receive the folowing error: Fatal error: Call to undefined function mysql_connect() in C:Program FilesApache GroupApache2htdocsfilea.php on line 8 My system's particulars are: 1. The system path on my system is set as follows: C:WINNTsystem32;C:WINNT;C:WINNTSystem32Wbem;C: Program FilesMySQLMySQL Server 4.1in;C:PHP5;C:PHP5ext; 2. The php.ini file is set to the full path of the 'ext' directory in my PHP installation, i.e. extension_dir = "C:PHP5ext" The "extension=php_mysql.dll" line is not commented in my php.ini file. 3. The information page from phpinfo() does not include a separate section called “mysql†as I believe it should. The extension_dir in phpinfo() is set to C:php5 Can anyone tell me what I am doing wrong?
View Replies !
Help, (Select...Group By..) Result Error
I have data like this : ord_empno | ord_date | ord_code 00136 | 1996-07-08 | 00010 00136 | 2003-12-31 | 00020 00136 | 2004-01-01 | 00070 I'd like to select data with the latest date (row 3). sql : SELECT ORD_EMPNO, MAX(ORD_DATE) ORD_DATE, ORD_CODE FROM TB_PSN_ORDER WHERE ORD_EMPNO = ?' GROUP BY ORD_EMPNO but the result 00136 | 2004-01-01 | 00010 What's wrong with that ? It seem it takes only the first ord_code.
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 !
Error 1305 - Trying To Execute User Defined Function
In my investigation, I attempted to execute the function (named 'esp') and I started getting the error message: (from database named XQ) SELECT esp(0); Error 1305 (42000): FUNCTION XQ.esp does not exist So from there, I attempted to re-install it: CREATE FUNCTION esp RETURNS INTEGER SONAME 'mysqlExtension.so'; I then get this back from mysql: FUNCTION 'esp' already exists. I also tried in vain: DROP FUNCTION esp; same message comes back: Error 1305 (42000): FUNCTION XQ.esp does not exist Not sure where this went wrong. It was working fine then something made it go amok. In one case it says it exists, the other it does not.
View Replies !
Fatal Error: Call To A Member Function On A Non-object
I am wondering why I am getting the following error: Fatal error: Call to a member function on a non-object in /home/blu6592/public_html/new/register2.php on line 46. Here is the code that is on line 46: PHP Code: $name_check = $db_object->query("SELECT username FROM comments WHERE username = '".$_POST['username']."'");
View Replies !
ERROR 1305 (42000): FUNCTION Dotproject.EFT Does Not Exist
what "ERROR 1305 (42000): FUNCTION dotproject.EFT does not exist" produced by the following means & how I fix it. mysql> SELECT LEFT(project_name,50) AS 'Project', -> /*== Decode Project Status == */ -> CASE project_status -> WHEN 1 THEN LEFT('Incoming/Approval',20) -> WHEN 2 THEN LEFT ('Planning',20) -> WHEN 3 THEN LEFT('In Progress',20) -> WHEN 4 THEN LEFT('QC/UAT',20) -> WHEN 5 THEN LEFT('Complete',20) -> WHEN 6 THEN LEFT('On Hold',20) -> WHEN 7 THEN EFT('Archived',20) -> END AS 'Status' -> from projects; ERROR 1305 (42000): FUNCTION dotproject.EFT does not exist
View Replies !
Fatal Error: Call To Undefined Function: Mysql_connect()
i have to phpBB forums, until yesterday everything worked fine until yesterday. all of a sudden i get this error message (sometimes) when browsing the forums: Fatal error: Call to undefined function: mysql_connect() in /home/gnahrung/public_html/forum/db/mysql4.php on line 48 i haven't changed any of the phpBB-files or the databases. when i get this error and i refresh the page the error goes away.
View Replies !
Fatal Error: Call To Undefined Function Mysql_connect()
I have recently installed ,on my OS Windows 2000, Apache Server 2,PHP 5 and MySQL Server 5.0(as Windows Service-starting automatically). Although I have passed through all steps (which I learned after reading many forum posts) to configure php.ini-recommended (including renaming and copying it to C:WINNT) and added libmysql.dll,php5apache2.dll,php_mysql.dll,etc to C:WINNTSystem32, it seems that smth weird is happening. On my screen appears :"Fatal error: Call to undefined function mysql_connect()" when I open a php file in my webbrowser, in order to test mysql connectivity.
View Replies !
Sum()/Group By Query Help
I have tried searching for a solution to this, but I can't seem to find a good set of keywords, so I'm hoping some kind soul will answer it for me. I'm not sure it is even possible, but it certainly seems as though it should be....
View Replies !
Group By Query
I have the following query that is dynamically generated: SELECT did, SUM(weight/3) as didweight FROM documentkeywords WHERE word = 'test1' OR word = 'test2' OR word = 'test3' OR word != 'test4' GROUP BY did ORDER BY didweight the idea is to select keywords and add their weights then devide by the number of keywords, the trouble is i wish to remove items from the result that have a word that is not supposed to be there (eg 'test4'). How can i do this?
View Replies !
Using GROUP BY In Query
I have a table with many fields and among them I have an index called `id` and a timestamp called `timestamp`.I want to be able to select rows according to my WHERE clause, but only access the rows with the greatest `timestamp` for each `id`. As if the table is really only composed of one instance of each value of `id`. So I am using the GROUP BY on `id` to limit the results to one row per unique id.Code: SELECT * FROM `person` WHERE `company_id` = 1 GROUP BY `id` ORDER BY `timestamp` DESC, `last_name`, `first_name` Which gives me one row per value of `id`, but I can't figure out how to get it to order by the latest timestamp in each grouping. The ORDER BY clause in the example doesn't work.I have tried approaches using DISTINCT and HAVING, but I can't seem to get it right.
View Replies !
'Group By' Query Help, Please
I'm having trouble with a query In using in conjunction with a Group By clause. SELECT ur.userID, r.referral, COUNT( r.referralID ) AS overall FROM referral AS r LEFT JOIN userreferral AS ur ON r.referralID = ur.referralID GROUP BY ur.userID LIMIT 0 , 30 The above query gets no more than 30 users, with each row representing a unique user. Each row has a count column that counts the number of "referrals" a particular user has, Group By is used so we only get one row per user. My problem is that say if a user has 10 referrals, the above query will return the first referral made to that user, My question is How can I make it so the above query returns the 10th referral (ie latest) referral made to that user?? I know theoretically all i need todo is reverse the referral column for each user so the last one is diaplyed first but I cannot work it out.
View Replies !
Using A Simple 'function' In SELECT Query
Is there any way of creating a SELECT query which has as it's result a column which is comprised of a function whos inputs are the values in other results columns. For a simple example - a four column output, x, y, x+y, x/y SELECT A.x, A.y, A.x+A.y, A.x/A.y FROM A This is works fine, as is, and produces the correct results. However, as the function gets more and more complicated and the A.x and A.y turn into sub-queires (and generally the expressions all become larger and then more unwieldy) not only does the function get very painful to write and debug but the query times rise rapidly also. Can alisas' be used here (or any other methods people know of) to refer to a columns' results to ease writing the functions and keep the load on the DB to a minimum?
View Replies !
Complicated Math Query: Function?
I have a database with different chemical/physical data for products we manufacture. I would like to calculate some statistical information from the viscosity data, and can't get my head around how to do it. I would like all the calculations to take place in MySQL if possible, 'cause 'I ain't no programmer.' I don't know if this will require a function or not. Here's the beginning of the query I've written: select row_id, viscosity, (select avg((viscosity)) from qc_ALL where formula=872 and chronos > '2005-09-16') as avg_visc from qc_ALL where formula=872 and chronos > '2005-09-16'; So far, so good. Here are the results, which are exactly what I want:
View Replies !
Executing External Function In A Query?
I got the following task: 1. There is a column "country", containing country codes (like "uk", "us", etc.) 2. I need to sort the table according to FULL NAMES of countries, based on those country codes, Example. Table contains: ....
View Replies !
Having Problems Using A Function To Call A Sql Query...
Function: function calldb($db_type, $query_type, $cat_link) { include 'dbconnect-include.php'; $DBName = $db_type; $Link = mysql_connect ($Host, $User, $Password); $Query = $query_type; //$NavLinks = "SELECT * FROM nav_links WHERE Cat_Links=$cat_link"; //$PriceCuts = "SELECT * FROM price_cuts WHERE Cat_ID=$cat_id"; $Result = mysql_db_query ($DBName, $query_type, $Link); if ($Result) { //echo 'The query was successfully executed!<BR>'; while ($Row = mysql_fetch_array($Query)) { print "<a href={$Row['Url']}>".$Row['Links']."</a><br>"; //echo $dbtype; } } else { echo 'The query could not be executed!<br>'; } mysql_close ($Link); } calling the function: calldb(maxforcepc, "SELECT * FROM nav_links WHERE Cat_Links=$cat_link", $_GET['CatID']); I keep getting The query could not be executed! echoed out.. IT was working correctly until i decided to turn it into a function too suite my needs. I think the problem is that it's not grabbing the variable intime to store it in the query statment. The reason why I say this is because I tried printing out $Query and i get the statement to print out but not with the number. Now if i just do echo $_GET['CatID']; it echoes the number that is in the url.
View Replies !
How To Write Group By Query
i create table item-info. i want to view my data in the following way. i write query use group by but i want name show only one time no repeatation name next line. item-info table: id name item price 1 A XX 15 2 B XX 16 3 A YY 17 4 C YY 20 5 B ZZ 50 6 A ZZ 40 View my data in the following way: name item price A XX 15 YY 17 ZZ 40 B XX 16 ZZ 50 C ZZ 20
View Replies !
Query A Specific Day, And GROUP BY
The query below, counts how many records each agent has entered in the previous 7 days and groups them by agent... Then it also counts a total for all the agents for the previous 7 days as well, not grouped. How can i query a specific day of the week, say 4 days back, only count that days records and group them by agent?
View Replies !
DISTINCT/GROUP Query.
I have some serious issues with a DISTINCT Query. The table is for logging users. Let's say the table looks like this userID | average | timestamp 1 | 20 | 2008-01-16 00:00:00 1 | 35 | 2008-01-17 00:00:00 2 | 12 | 2008-01-18 00:00:00 1 | 16 | 2008-01-20 00:00:00 What i want to go get is the the DISTINCT userID (every user, but only one time) and i want to get the latest value of the avarage field (so sorted by timestamp desc). I tried many ways to get this but i can't seem to get it to work, DISTINCT is using all the fields i add, and if i GROUP i either have to say like, MAX/MIN and since there is nothing that makes the average increase/decrease by every new post in the database. My third try was to order by the timestamp, but i doesnt seem to work in all cases, only sometimes.
View Replies !
How To Group/merge Query
I'm trying to query from a specific column in the table. But there will be repeated values. I wonder if there's any function I can use to "merge" the same ones and end up just a list of all the non-repeating values. Don't know if I make sense here... hope the following helps: Column A -------- apple pear orange apple orange orange cranberry and I want a query that gives me "apple, pear, orange, cranberry" only.
View Replies !
Last_insert_id() Function INSIDE An INSERT Query
so i've got an insert query, puts a reference to a file on the system in a table. good, great. Now i've got another table ( a join table?) that has 3 columns (id, fileId, entryId) that i need to make an association on. I've got the entryId, but the fileId is what I don't have but can get with the last_insert_id() function but don't know how to 'use' it. INSERT INTO siteentryattachements(entryId, fileId) VALUES ('#ARGUMENTS.entryId#','SELECT LAST_INSERT_ID() as fileId');
View Replies !
|
TRACKER
