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 Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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
Invalid Use Of Group Function
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.... I tried to do the following: select sum(timediff(max(vTransTime),min(vTransTime))) as worked, sum(timediff(max(translog.to1),min(translog.from1))) as required, sum(timediff(sum(timediff(max(vTransTime),min(vTransTime))), sum(timediff(max(translog.to1),min(translog.from1))))) as overtime, empid,deptname, firstname,lastname from employee,translog,department where employee.emp_magnetic_num=translog.vId group by employee.empid I am getting the following error : Invalid use of group function
Invalid Use Of Group Function
Using mysql 4.0 I get the following error message "Invalid Use of group function" with the following code: update custRP c, tbl_acctbal a set c.rpBal = sum(a.currRPBAL) where c.custid = a.custid
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();
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.
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
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. =....
Group Function
I have a table with the below model. SN Parent Child amount 1 A01 A02 1000 2 A01 A03 1000 3 A02 A04 500 4 A03 (null) 1000 5 A04 (null) 200 5 A05 (null) 1500 How can I write the query to capture the sum of amount on A01 and A05 Parent Sum of amount A01 3700 A05 1500 Where 3700 is sum of 1000(A01-A02), 1000(A01-A03), 500(A02-A04), 1000(A03-null), 200(A04-null)
Group Function
Why can't I use a group function when I update? UPDATE table1, table2 SET table1.activity_count = count(table2.activity_id) WHERE table1.user_id = table2.user_id GROUP BY table1.user_id I get an "Invalid use of group function" error. I've tried this kind of thing in several queries & got the same error, so it's not a problem with this particular query. Is there any way to get around this without getting the results and using a loop to insert?
MIN Function In A GROUP BY Statement
I have simplified my problem as follows: Imagine a table containing details of play performances: CREATE TABLE performances ( id INT PRIMARY KEY , when DATE , play VARCHAR(255) , theatre VARCHAR(255) )
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
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 | +------+----------------+----------------------------+------------------------------------------------------+
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)
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?
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.
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.
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?
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.
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.
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.
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.
Fatal Error: Call To Undefined Function Mysql_connect()
I downloaded PHP5 and MySQL 4.0.20A and installed them. However, I could not use my php script to create a database and got the undefined function call error. I would appreciate if someone could show me how to enable MySQL to communicate with php.
Fatal Error: Call To Undefined Function Mysql_connect()
found a couple of posts about this but still couldnt fix this problem basically, ive installed apache and php 5 all works great: then i installed mySql, had alot of problems installing it but finally got the service running correctly. when i run my php script it kicks up this error Fatal error: Call to undefined function mysql_connect() ive checked that all the SQL dlls are in the htdocs folder aswell as making sure that the php.ini (this is in htdocs aswell) has the extensions mysql and mssql.dll uncommented. and the my.ini file in C:/windows is : [mysqld] # set basedir to your installation path basedir=C:/Program Files/Apache Group/ # set datadir to the location of your data directory datadir=C:/Program Files/Apache Group/data [WinMySQLAdmin] Server=C:/Program Files/Apache Group/bin/mysqld.exe ive rebooted and also restarted my services and still get the error.
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']."'");
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 )
Invalid Use
I am having a problem with group. Following is my query but it gives an error of "Invalid use of group". SELECT refMember.MemberAcroName, refMember.MemberName, Count(DistrictLevelInformation.FormID) AS Spread FROM (refMember LEFT JOIN MainInformation ON refMember.MemberID = MainInformation.MemberID) LEFT JOIN DistrictLevelInformation ON MainInformation.ID = DistrictLevelInformation.FormID GROUP BY refMember.MemberAcroName, refMember.MemberName, MainInformation.qYear, MainInformation.Quarter ORDER BY MainInformation.qYear DESC , MainInformation.Quarter DESC , Count(DistrictLevelInformation.FormID) DESC
Invalid Use
I have tested my query on MySQL 5 and it works, sadly the server it is to be run on only has 3. I was just wondering what I need to update in the following query to get it working on 3 or if it is even possible to get it running on 3. Code: ..... and keyword IN ('keywords') group by people having count(*) >= 1 ORDER BY people ASC
Invalid Index
I try to used the MyPhpAdmin 2.8.2.4 with PHP 4.4.4, MySQL5.0.2.4 and Apcache2 and I got this error, 'invalid Index'
Directory Name Invalid
For testing purposes I have PHP installed and I have MySQL installed on my local laptop. Windows XP Pro I am just learning both of them. I have read quite a few chapters in the MySQL Manual. Enough to allow me to get around in MySQL and directly create tables an view information in them, upload .txt files into the tables, etc etc. I am receiving an error when I try to connect to MySQL via a PHP script. I have no problem connecting to MySQL via Command Line. The error I receive is "The directory name is invalid." Here is my code... I am not sure where to put the directory name or where to put the directory path in order to make it valid. I am not sure what directory it is talking about. <html> <head><title>Connect to MySQL</title></head> <body> <?php //connecting to the mysql server uses a mysql_connect command. $link = mysql_connect("locoalhost",$_POST['username'],$_POST['password']) or die("Connect Error: ", . mysql_error()); echo 'Successfully Connected. '; mysql_close($link); ?> </body> </html> Where exactly would I put the path to the directory or where would I name the directory?
Invalid Syntax
I am having trouble changing my password back to OLD_PASSWORD by the following : mysql> SET PASSWORD FOR -> 'some_user'@'some_host' = OLD_PASSWORD('newpwd'); When I do it, it says ERROR 1064: 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 'OLD_PASSWORD ('newpwd')' at line 2
Invalid Backup File
I am tring to restore a .pmb backup file and getting the message "Invalid backup file". I am very new to myssql so I don't have a clue what that means. Wrong version of mysql? Corrupted file?
Invalid Argument While Using BCP (Script)
This will be a 50/50 question. It's either something that the SQL experts can answer or a scripting forum. I'm new to SQL and just coming to grips with getting familiar with scripts. I've developed a small script for my workplace that will connect to a remote server and extract the specified table that I want, zip it up and then copy it back to my local machine. This works fine until it tries to run the bcp command. If I tivoli to the remote machine and type the bcp command in command prompt it works fine. However when executed from the script it throws an invalid argument and seems to refer to the "-" minus I place in front of the U (user) or P(pwd). The script echos the users table request to a file which is then copied to the remote server (SQLtable.cmd) and then PSEXEC is called to connect to the server and run the script. e.g. psexec \%Server% -u %user% -p %password% "C: empSQLtable.cmd" PSEXEC then returns the following error. C:WINNTSYSTEM32>Bcp FrontOff..SHIFT_TER_DATA out C: empdmpnlSHIFT_TER_DATA.dat ûUsa ûPpwd (chopped off the -n) Unknown argument '¹Usa' on command line. Sorry about all the data, just hope someone can help. Must be an easy way. The small script which psexec is executing contains two simple lines. Bcp FrontOff..SHIFT_TER_DATA out C: empdmpnlSHIFT_TER_DATA.dat -Usa -Ppwd -n wwzip -9 C: emp\%username%SQLtables.zip C: emp\%username%*.*
Invalid Characters With PHP And MySQL
I am writing about a situation that our client is currently experiencing with Linux Mandrake, Apache, MySQL and PHP. We successfully deployed a website running on this type of system for a period close to two years. It has a HTML GUI component that the client can type or paste content into and it serves-up dynamic pages in PHP from a MySQL database. Recently the web site was hacked by the Travel Bug virus and it forced the client to restore the web site and server software mentioned below. Original Install Mandrake 8, MySQL 3.23.32, PHP 4.0.06 and Apache 1.3.26-x.x Current Install Mandrake 9.0, MySQL 3.23.52-1, PHP 4.2.3-1.1 and Apache 1.3.26-6.1 The current install has lead to new problems. Content with double and single quotes are showing up as squares or question marks. For example, I have a page that is showing the square symbol in place of double quotes. If I do a view source on that specific page I can see the double quotes but if I telnet into the server and query the MySQL record belonging to that page the square or double quote character is nowhere to found. The following items are the steps we tried to understand why this is happening. First, we did the following to test the integrity of the MySQL backup file. We Restored the MySQL database on our test environment which has the original software specs mentioned above but on a window server running IIS not apache. I did a restore of the MySQL database file and did not get any invalid characters. Even though the environment isn’t completely identical to the clients we can make the assumption that the dump file wasn’t corrupted. Second, possibly content editors were copying and pasting from word into the HTML GUI component thus creating these invalid characters. In most cases yes but I personally noticed the invalid character issue with a page that doesn’t use the HTML GUI component. Third, Possibly the configuration especially the character set from the original version to the current version of MySQL is not configured correctly. We believe this to be the primary issue. The resolution is to configure or reinstall MySQL and restore from backup. Fourth, has anyone tried the PHP functions to convert the invalid characters. A request was to look into the iconv() function. The iconv library functions convert strings between various character sets encodings. This involves compiling the PHP interpreter which is why I haven’t tested it. Fifth, Can some other process running on the server be causing this? Altogether, we soon will begin creating a test server identical to the original settings and step through the install and try to recreate the invalid character issue. But, if anyone has experienced this invalid character issue or has more information that would benefit us your help would greatly be appreciated.
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?
Valid/Invalid Column Names
I'd like to present tabular data in HTML with the first row being the names of the columns in my database. However, I'd like them to be more presentable; for example 'I.P. Address' instead of 'ip_address'. I was wondering if a name like 'I.P. Address' could ever somehow be a valid column name or not.
Invalid MySQL-Link Resource
I'm getting the error message 'Supplied argument is not a valid MySQL-Link resource' (applies to the 3rd line of code) when the code below executes: $delete_part_no_query = "DELETE FROM prices WHERE part_no == '$part_no_to_delete'"; $delete_part_no_result = mysql_query($delete_part_no_query); $num_delete_part_no_result = mysql_affected_rows($delete_part_no_result); The query works fine and I'm under the impression that 'mysql_affected_rows' should be used to return the number of rows affected by DELETE. However, I keep getting the above error message.
Invalid Distinct Recordset Returned On An Indexed Column
I have a table with about 1.2 million records. I have an index set on a column. For close to two years, this query has worked perfectly fine: SELECT DISTINCT `Mgmt_Area` as thevalue, `Mgmt_Area` as valueid from qcdata ORDER BY thevalue Note, this is a programmatically generated query based on some user selections. This query actually is used to create a listbox. The index has 62 separate values. This query would usually return: Code:
Error Message Warning: Printf() [function.printf]
In my php code i have... <?php mysql_connect("localhost", "my-username", "my-password") or die(mysql_error()); mysql_select_db("my-database") or die(mysql_error()); $result = mysql_query("SELECT * FROM beavers_news ORDER BY posted DESC"); if ($row2 = mysql_fetch_array($result)) { do { Printf("<table width='100%'><tr><td>%s</td></tr><tr><td>Posted: %s</td></tr><tr><td>%s</td></tr></table>", $row2[title], $row2[posted], $row2[news]); }while ($row2 = mysql_fetch_array($result)); }else{ echo "There is no news!"; } ?> And i add news into the mysql database, but when i go to view the page i get the following error message... Warning: printf() [function.printf (http://www.southwaterseascouts.org/scouts/news/function.printf)]: Too few arguments in /home/sites/southwaterseascouts.org/public_html/scouts/news/index.php on line 55
Warning: Mysql_data_seek(): Offset 0 Is Invalid For MySQL Result Index 2
I only posted this on the MySQL forums becouse i believed it had to do with the query. I get the following error: Warning: mysql_data_seek(): Offset 0 is invalid for MySQL result index 2 (or the query data is unbuffered) mysql_data_seek is used in a function (made not by me) which i know 500% that it works for other queries. The query in PHP is this:
GROUP BY With Order Inside Group
I have a problem when grouping records - I can't manipulate data inside group. For example, I have table `images` with fields `name` (name of image, not unique) and `dtadded` (date of image adding). Then, I need to get all images names with distinct names where each name must be latest added name. Sample: ------------------------------ id, name, dtadded ------------------------------ 1 name1 2007-10-15 00:00:00 2 name2 2007-10-15 00:00:00 3 name1 2007-10-16 00:00:00 ------------------------------ I need to receive 2 results (for each name) 3 - name1 - 2007-10-16 00:00:00 2 - name2 - 2007-10-15 00:00:00 If I use SQL code: SELECT images.id, images.dtadded, images.name, count(name) FROM images GROUP BY `name` ORDER BY dtadded DESC I get results 1 - name1 - 2007-10-15 00:00:00 2 - name2 - 2007-10-15 00:00:00 It groups records with first row in database, but I need last row in table for each name. Question: How can I order results in side group to get needed results as described above?
"group By" Error
Im trying to output the country name where the population is less than the sum of all the cities populations within that country. Also, to output the difference between the two population values. Here is what i have: select c1.name, sum(c2.population)-c1.population from cities2 c2, country c1 where c1.c_id=c2.country group by c1.name having sum(c2.population)>c1.population; But i keep getting errors about the group by expression.
Top Record From Each Group In GROUP BY
I never could figure out a good way to do this. I am doing a group by statement, and want the top record from each group to be returned only. My query is to find the current win/loss streak for each team. Here is the sql: SELECT id, team_name, result, MIN(date) AS startdate, MAX(date) AS enddate, COUNT(*) AS games FROM ( SELECT t.id, t.team_name, sched.date, CASE sched.draw WHEN 1 THEN 'draw' ELSE CASE sched.winner_id WHEN t.id THEN 'win' ELSE 'loss' END END AS result, ( SELECT COUNT(*) FROM fantasy_schedule sched2 WHERE ( CASE sched2.draw WHEN 1 THEN 'draw' ELSE CASE sched2.winner_id WHEN t.id THEN 'win' ELSE 'loss' END END ) <> result AND sched2.date >= ��-01-01' AND sched2.date < ��-01-01' AND sched2.date < sched.date AND sched2.fantasy_league_id = 6 AND (sched2.fantasy_team1_id = t.id OR sched2.fantasy_team2_id = t.id) ) AS rungroup FROM fantasy_schedule sched JOIN fantasy_team t ON t.id = sched.fantasy_team1_id OR t.id = sched.fantasy_team2_id WHERE sched.last_update < NOW() AND sched.date >= ��-01-01' AND sched.date < ��-01-01' AND sched.fantasy_league_id = 6 ) bighonker GROUP BY id, team_name, result, rungroup ORDER BY enddate DESC And a subset of what is returned: +----+----------------------------+--------+------------+------------+-------+ | id | team_name | result | startdate | enddate | games | +----+----------------------------+--------+------------+------------+-------+ | 1 | The Macho King | loss | 2005-12-17 | 2005-12-17 | 1 | | 1 | The Macho King | win | 2005-12-11 | 2005-12-11 | 1 | | 1 | The Macho King | loss | 2005-11-24 | 2005-12-04 | 2 | | 1 | The Macho King | win | 2005-11-20 | 2005-11-20 | 1 | | 1 | The Macho King | loss | 2005-11-13 | 2005-11-13 | 1 | | 1 | The Macho King | win | 2005-10-30 | 2005-11-06 | 2 | | 1 | The Macho King | loss | 2005-10-16 | 2005-10-23 | 2 | | 1 | The Macho King | win | 2005-10-09 | 2005-10-09 | 1 | | 1 | The Macho King | loss | 2005-10-02 | 2005-10-02 | 1 | | 1 | The Macho King | win | 2005-09-08 | 2005-09-25 | 3 | | 2 | General Grievous | loss | 2005-12-17 | 2005-12-17 | 1 | | 2 | General Grievous | win | 2005-12-11 | 2005-12-11 | 1 | | 2 | General Grievous | loss | 2005-11-24 | 2005-12-04 | 2 | | 2 | General Grievous | win | 2005-11-13 | 2005-11-20 | 2 | | 2 | General Grievous | loss | 2005-11-06 | 2005-11-06 | 1 | | 2 | General Grievous | win | 2005-10-23 | 2005-10-30 | 2 | | 2 | General Grievous | loss | 2005-10-16 | 2005-10-16 | 1 | | 2 | General Grievous | win | 2005-10-02 | 2005-10-09 | 2 | | 2 | General Grievous | loss | 2005-09-08 | 2005-09-25 | 3 | | 3 | Ultimate Jarin | win | 2005-11-06 | 2005-12-24 | 8 | | 3 | Ultimate Jarin | loss | 2005-10-23 | 2005-10-30 | 2 | | 3 | Ultimate Jarin | win | 2005-10-09 | 2005-10-16 | 2 | | 3 | Ultimate Jarin | loss | 2005-09-25 | 2005-10-02 | 2 | | 3 | Ultimate Jarin | win | 2005-09-08 | 2005-09-18 | 2 | | 4 | The Hulkamaniacs | loss | 2005-12-11 | 2005-12-11 | 1 | | 4 | The Hulkamaniacs | win | 2005-12-04 | 2005-12-04 | 1 | | 4 | The Hulkamaniacs | loss | 2005-11-24 | 2005-11-24 | 1 | | 4 | The Hulkamaniacs | win | 2005-11-06 | 2005-11-20 | 3 | | 4 | The Hulkamaniacs | loss | 2005-10-30 | 2005-10-30 | 1 | | 4 | The Hulkamaniacs | win | 2005-10-09 | 2005-10-23 | 3 | | 4 | The Hulkamaniacs | loss | 2005-09-18 | 2005-10-02 | 3 | | 4 | The Hulkamaniacs | win | 2005-09-08 | 2005-09-08 | 1 | | 5 | WHO WANTS TO KISS HOMER? | win | 2005-12-04 | 2005-12-04 | 1 | | 5 | WHO WANTS TO KISS HOMER? | loss | 2005-11-20 | 2005-11-24 | 2 | | 5 | WHO WANTS TO KISS HOMER? | win | 2005-11-13 | 2005-11-13 | 1 | The top record in each group is the current streak. I want only that top record for each teamr returned. I could simply 'order by enddate DESC' and in my php script loop until I have one record per team, but is there any way I can only return the top result for each team in the query itself? Here is the corresponding table schema: +-------------------+---------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------------------+----------------+ | id | int(10) unsigned | | PRI | NULL | auto_increment | | fantasy_league_id | int(10) unsigned | | MUL | 0 | | | week_num | tinyint(3) unsigned | | MUL | 0 | | | fantasy_team1_id | int(10) unsigned | | MUL | 0 | | | fantasy_team2_id | int(10) unsigned | | MUL | 0 | | | winner_id | int(10) unsigned | YES | | 0 | | | date | date | | | 0000-00-00 | | | last_update | datetime | | | 0000-00-00 00:00:00 | | | draw | tinyint(3) unsigned | | | 0 | | +-------------------+---------------------+------+-----+---------------------+----------------+ fantasy_team +---------------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-----------------------+------+-----+---------+----------------+ | id | int(10) unsigned | | PRI | NULL | auto_increment | | fantasy_league_id | int(10) unsigned | | MUL | 0 | | | user_id | mediumint(8) unsigned | | MUL | 0 | | | team_name | char(40) | | | | | | fantasy_division_id | int(10) unsigned | YES | MUL | NULL | +---------------------+-----------------------+------+-----+---------+----------------+
GROUP BY Only Displaying 1 Row Within Each Group
I have a database that consists of various sports schedules. The part that is NOT working is GROUP BY. I am trying to group by a column titled "sport" My current query: SELECT * FROM `otis_sports` WHERE season = 'Fall' GROUP BY sport
Now() Function
I am using now function to get current date when info is added to my table. I am using a hosting provider that is 6 hrs ahead in time zone so i always have to subtract 6hrs to get the correct time. Is there anyway I can format the time to be inserted to my time zone?
|
TRACKER
