Why Does GROUP_CONCAT() Function Fail In MySQL 4.1.12 In This Query?
Here are my results using MySQL 4.1.12 and I honestly don't think this is right, but can't figure out what to do:
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
GROUP_CONCAT Problem In 5.0.24 (query Works In 5.0.18)
So on my production server they are running MySQL - 5.0.24-standard. Locally I'm only running 5.0.18 but didn't figure it would be much of a problem, until I noticed that my "results" from certain searches were different with the exact same data on each MySQL version. The query is this:
Fail To Run A Script Containing Mysql -e
I am a dummy in mysql and start playing with it. My question is when I try to write a simple script: data=`cat data.txt` mysql -ppassword -e 'use db_1;update table_1 set Value = '$data' where Field_1 = "some_value" and Field_2 = "some_other_value";' when run the script, I got error: ERROR 1049: Unknown database '12345 where Field_1 = "some_value" and Field_2 = "some_other_value";' clearly mysql interpreted 'use db_1;update table_1 set Value = ' as the command to execute but ignored the string beyond the symbol '. I have tried to change '$data' to "$data" but it didn't work.
Is There An 'Is Negative' MySQL Query Function?
I have a query, as shown below. Its a grouped query and I have a column called quantity in a products table. If the value in this table drops below zero I want the query to take zero as the quantity. Is this possible?.....
GROUP_CONCAT Alternative For MySQL < 4.1 ?
GROUP_CONCAT is a great function...But have MySQL version < 4.1 and my hosting provider can't upgrade MySQL. Is there a way to get this result with a different SELECT query? I have a list of recipes in one table, with a list of ingredients in another. Since there are a different amount of ingredients per recipe, the 2nd table has multiple ingredients per recipe.....
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 )
Alternative To Group_concat For Early Versions Of Mysql
Is there an alternative to group concat, if you are using a lower version of mysql. If any one has any ideas it would be great, currently my code (thanks to some one from these forums) looks like this. SELECT c.clientID, group_concat(ci.interestID) as interests FROM Clients AS c JOIN ClientInterests AS ci ON c.clientID = ci.clientID GROUP BY c.clientID HAVING find_in_set(1, interests) AND find_in_set(3, interests) Is there any way of doing this on a lower version of mysql? Is it possible to wirte a function similar to group_concat?
Login Fail
wrong message: （ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)） 设置ROOT密码后，登陆失败,请问是什么原因？ why login fail,after I set up root's password?
Connection Fail
windows 98 apache 1.3 php4 mysql ....had it all running and could use php with mysql and everything......used it today and cant for the life of me get mysql working.i do the following:- c:Windows> cd c:mysqlin c:mysqlin> mysqld c:mysqlin>mysql -u root -p enter password: ******* ERROR 2003: cant connect to MySql on server 'localhost' (10061) Can anyone see where i am going wrong please?
Fail To Connect
I have a problem where one of my mysql servers fails to connect for no apparent reason - I find that if I re-enter a 'grant' statement for the connecting user and host and then flush privileges, that everything works again. Has anybody come across this before and know what to do about it? my my.cnf looks like this:Code: [mysqld] datadir=/export/data log=/var/log/mysql-messages set-variable = max_connections=100
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?
Fail Loading Data
I have a file creted using notepad where fields are separated by tabs. I try to load it using LOAD DATA LOCAL INFILE '/../name.txt' INTO TABLE mytabke I get the following message: Data truncated for column 'column name' ay row 1 error 1265 and this is repeated for al columns in the windows file.
E FAIL STATUS ERROR
I am using ADO and VB6 With MySQL 5. Some times when i am selecting records from my table it returns an Error MySQL ODBC Error Returned WIth an E FAIL Status. i am executing the query "SELECT * FROM MyTable" it will work some times without this error.
Fail To Add Data Into Table
I'm trying to create a table and insert with calculated data below, but have a problem to add calculated data into my table. Code:
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: ....
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.
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:
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?
Why Does This Simple Join Fail To Work?
I don't get any errors but the returned rows aren't the ones i expect from this query. When i run the query below i get only two rows from news (2 and 5) while i would expect to receive all rows from news with either 0 or 1 for commentcount I've narrowed down the problem to the join but i fail to see why the join excludes the other rows? Can anyone help me out with this one? Using MySQL 4.1.20 My Query: SELECT news.id, news.title_nl, COUNT(news_comments.id) AS commentcount FROM news LEFT JOIN news_comments ON news_comments.news_id = news.id GROUP BY news_comments.news_id news Table: CREATE TABLE `news` ( `id` int(11) NOT NULL auto_increment, `title_nl` varchar(255) default NULL, `title_fr` varchar(255) default NULL, `body_nl` text, `body_fr` text, `source` varchar(255) default NULL, `user_id` int(11) default NULL, `status` tinyint(1) default Ƈ', `type` varchar(200) default 'uitgaan', `created` timestamp NULL default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `news` VALUES (ƈ', 'A little on me ...', null, 'Brokerage, regal, symphony gold status housekeeping panoramic respectable status champagne gem saphire panoramic. Metropolitan le doctoral accredited marquis world lifestyle. Fine, echelon wealth de-jour ambassador lifestyle member gem wine portfolio affluent cruise. Wine gilded dynasty noble architectural opera travel cigar luxury ballroom echelon. World, genuine gilded de-jour first-class member theatre inspiring yacht. Noble distinctly suite auction philanthropic club designer echelon regal. Benefiting board imported benefiting stockmarket in cocktail portfolio diplomatatic fine townhome. Diplomatatic townhome, tailored gold penthouse club reserved monogram pearl champagne, acumen symphony affluent le. Yacht stockmarket expensive grande, travel guests monogram diamond status in opulent doctoral gentlemen opulent imported. Opera inspiring five-star accredited champagne sheer designer club career. Enthusiast panoramic handmade gifted pedigree lifestyle architectural investments benefactor acumen world housekeeping wine university dynasty. Investments, cuisine, cutlery private, auction marquis caviar imported elegant, rare, cutlery member diamond panoramic elegant. Investments, butler rare housekeeping art investments in brokerage ladies enthusiast portfolio bonds. Private, architectural benefiting fashion with cigar member polo, using upper. Dynasty enthusiast travel, symbolizing sport using, de-jour upper portfolio cuisine gifted private echelon le repertoire. Wine repertoire salon yacht, university travel board european echelon university gifted, luxury wine de-jour status. Gilded art on dynasty private, townhome. ', null, 'Den TVgids', ƈ', Ɖ', 'uitgaan', ��-06-22 11:13:05'); INSERT INTO `news` VALUES (Ɖ', null, 'Nieuws Test', null, 'Dit is een test van het nieuws, eens zien of het fatsoenlijk marcheert!', 'Inventis gazet', Ƈ', Ɖ', 'uitgaan', ��-06-13 11:15:55'); INSERT INTO `news` VALUES (Ɗ', 'Datum testen', null, 'Conventio olim huic velit, enim vulputate interdico jumentum ymo, si conventio, feugiat luctus. Dignissim jus, nimis abbas importunus quis conventio dolore. Feugait et neque vindico aliquip quod sed sudo humo exerci nonummy. Iriure caecus caecus delenit, aliquip praesent, facilisi iusto oppeto decet, vindico velit. Facilisi distineo demoveo ea ea eum abigo damnum. Nulla vel ullamcorper nulla nisl quis bis euismod hendrerit regula lenis. Ymo duis in oppeto importunus damnum blandit nimis gemino minim aliquip quadrum. Valde ad praesent dolore causa consequat abdo haero duis bene in quis ingenium duis, huic. Augue mauris dolus vindico hendrerit, roto demoveo ulciscor, delenit. Nonummy autem praemitto, enim erat abico. Vero plaga eu illum acsi ut transverbero wisi secundum hos. Premo aliquip multo os vel, lucidus feugait, illum lenis laoreet ludus. Typicus bene opes suscipit odio fatua hos dolore hendrerit decet. Praesent magna ullamcorper eu ne secundum nisl nimis incassum reprobo obruo mauris nisl blandit feugiat. Loquor tincidunt erat, utinam nimis ideo epulae, vulputate, et ideo elit eum. Acsi, in demoveo esca zelus mos roto ut quis demoveo tation letatio interdico veniam. Erat consectetuer tation duis nulla ullamcorper. ', null, null, Ƈ', Ɖ', 'muziek', ��-06-14 11:22:24'); INSERT INTO `news` VALUES (Ƌ', 'Nog een test van de timestamp', null, 'Timestamps in mysql ... argh!Ideo pecus, pagus sudo autem enim. Enim odio, turpis et velit imputo nobis. Neque ludus multo nulla valde importunus ea. Appellatio illum abico, duis, augue tego interdico exerci quia veniam rusticus nulla multo. Nulla jus, capto mara immitto aptent vel wisi abluo ut accumsan iaceo. Facilisi iusto euismod lobortis, torqueo hos suscipit singularis hendrerit in. Occuro usitas fere minim usitas jumentum facilisi iriure facilisis ea te ut ad cui. ', null, null, Ƈ', Ɖ', 'uitgaan', ��-06-26 11:06:07'); INSERT INTO `news` VALUES (ƌ', 'Test nieuwtje', null, 'Dit is een test van een nieuwsbericht met een <a href="http://www.google.com">Link naar google</a> en wat <strong>tekst in het vet gedrukt</strong>. Een nieuwe paragraag behoort ook tot de mogelijkheden', null, null, ƈ', Ɖ', 'muziek', ��-06-14 10:17:15'); news_comments Table: CREATE TABLE `news_comments` ( `id` int(11) NOT NULL auto_increment, `news_id` int(11) NOT NULL default Ɔ', `user_id` int(11) NOT NULL default Ɔ', `created` timestamp NULL default CURRENT_TIMESTAMP, `body` text NOT NULL, `status` tinyint(1) NOT NULL default Ƈ', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `news_comments` VALUES (Ƈ', Ƌ', ƈ', ��-06-26 16:12:02', 'Dit is een test reactie', Ɖ');
Upgrade Probs To 5.0.18 - Queries Fail
I'm hoping someone on this forum can help me. I use hibernate with java to access mysql. We are trying to upgrade from 4.1.15 non-inndb, to 5.0.18 with innodb. Hibernate makes a 'create tables' script. The script installs, but the queries that hibernate creates now fails. These same queries worked fine with 4.1 . Since this is a hard problem to solve on a forum, if any kind fellow would like to, you could install the db from this zip:
Fail Save System Task
i have a task which can take up some amount of times, which is upload a .csv file, and import those content into database. I would like to know whether a fail save design can be perform there. Requirement 1. the script will resume (or start all over again) no matter which service down(apache/mysql) 2. Script auto resume witout human interaction after server restart complete 3. Data record content (from .csv) inserted into those several table tally(using transaction) 4. Mail notification (i really have no idea if the mail server down too :( ) Can someone give me some idea how to come out this kinda things?
Network Marketing Efforts Are Fail
Base on study made by one international marketing consultant, some of the reason why most of network marketing effort are fail :- 1) No clear explanation of what the website or business is about 2) Forms were asking for contact information without telling them what you're going to do with their information and "What's In It For them" 3) People need a proof that what the site says is true. 4) The need for understandable explanation of the compensation plan. 5) No indication of who, or WHAT, is behind the site.
Replacing Tables Via A Fail-safe Method
Each month I receive a XLS file containing a customers stock list for that month, which is converted to a CSV file, and then processed into a table via PHP scripts. Rather hazardously, this process first involves truncating the previous contents of the table, with the new data placed in 'over the top'. Can you recommend a method of performing this operation in PHP which will only truncate and proceed with the changes if the new table data has first been confirmed valid?
X IN GROUP_CONCAT ?
I have a query that, a bit simplified, looks like this: SELECT positions FROM (SELECT tbl.id, (GROUP_CONCAT(tbl.foo) AS positions FROM tablefoo AS tbl LEFT JOIN tablebar AS bar ON bar.lostteethbaseid = tbl.id GROUP BY tbl.id ) AS test WHERE NOT (15 IN (positions)) AND NOT (16 IN (positions)) See what I want to achive is to retrive all positions from a table using GROUP_CONCAT and then check if certain values are present in the list of positions. Positions could look something like 15,27,30. However 15 IN (positions) does NOT work like 15 IN (15,27,30) but rather like 15 IN (ཋ,27,30') I got the query to work using LIKE: positons LIKE '%15%' But I would really like to use IN instead since that would be much quicker on an indexed field. Worth noting is that casting the field "positions" from BLOB to CHAR does not help.
GROUP_CONCAT LIKE 2 NOT LIKE 20 Etc?
I have used group_concat to consolidate all the 'genreids' as follows in a field... 2, 3, 4 How would i perform a search of this field, so that if i wanted to find genreid 2? But not 20, 21, etc? At current ive been using: ....AND genreids LIKE '%2%' However the above obviously matches too much :/
Group_concat
I am attempting to pull data from multiple tables into a resultset that I can use as an Excel spreadsheet for reporting. Code:
GROUP_CONCAT Bug
I am using the GROUP_CONCAT() as follows: SELECT (SELECT CONCAT(ProjectName) FROM Project WHERE FIND_IN_SET(ProjectID, RelatedProjectID))AS RelatedProject We have a project tracker system and we can have one project be related to several different projects. the issue is when there is only 1 related project associated with another project, this query does not work. Is there some sort of bug with GROUP_CONCAT?
Group_concat
mysql 4.1 I'm trying to use the group_concat function together with IN and I get a message "Invalid use of group function" How else can I accomplish this? "AND user.class_id IN (0, group_concat(classes.class_id))"
Group_concat
is there some limitations on data size when doing group_concat
GROUP_CONCAT Bug
I am using the GROUP_CONCAT() as follows: SELECT (SELECT CONCAT(ProjectName) FROM Project WHERE FIND_IN_SET(ProjectID, RelatedProjectID))AS RelatedProject We have a project tracker system and we can have one project be related to several different projects. the issue is when there is only 1 related project associated with another project, this query does not work. Is there some sort of bug with GROUP_CONCAT?
Something Like GROUP_CONCAT()
basically, i want my query to do something like this would do.Code: SELECT listings.*, GROUP_CONCAT(photos.pic SEPARATOR ','), listings.office_id FROM listings, offices JOIN photos ON photos.list_id = listings.ln WHERE listings.office_id = offices.office_id AND ln = '$ln' GROUP BY photos.pic but that doesn't work work in 4.0.x, does anyone know how i can get the same affect in mysql 4.0.x?
Issue With GROUP_CONCAT
A table looks like: time millisec 10:02:04 234 10:02:04 567 10:04:12 003 Both time and millisec are in order by simply select. In fact, I want to use the counter to do row operations, like different between two adjacent rows. Maybe one has better idea than adding a counter.
Complement Of Group_concat
is there a complement function of the "group_concat" function? for example: id,items 1,'2,45,5' 2,'5,6' => convert into that format id,items 1,'2' 1,'45' 1,'5' 2,'5' 2,'6' i need to use mysql 4.1.22 (no stored procedures...)
Alternative To GROUP_CONCAT
I have a db of events with categories and locations. A form w/ checkboxes inserts into 2 tables the following: Table 1: event (id, title, date, description, contact) Table 2: event_search (only foreign keys) (event_id,location_id,type_id) 2 other tables are listing the locations and the types Table 3: location (International, national, all 50 us states) (id, name) Table 4: event_type (promotional, free, concert...) (id, name) Some events have multiple types and locations, so this is the only way I've figured out to have a normalized db. How can I list the events, displaying their locations and type, without using GROUP_CONCAT (only works w/ mySQL 4.1 :( )
GROUP_CONCAT Emulation
Does anyone know how to emulate the GROUP_CONCAT function for versions before it was added?
GROUP_CONCAT Crossjoin Problems
I've been searching the forums and found this thread which seems very similar to my problem, but I'm having trouble adapting it due to the complexity of the example. Here's my demo table and problem query: CREATE TABLE items (id INT, name CHAR(5)); INSERT INTO items VALUES (1,'test'); CREATE TABLE gallery (img_id INT, item_id INT ); INSERT INTO gallery VALUES (1,1); INSERT INTO gallery VALUES (2,1); INSERT INTO gallery VALUES (3,1); CREATE TABLE gallery2 (img_id INT, item_id INT ); INSERT INTO gallery2 VALUES (10,1); INSERT INTO gallery2 VALUES (20,1); INSERT INTO gallery2 VALUES (30,1); SELECT items.*, GROUP_CONCAT(gallery2.img_id) AS gallery2, GROUP_CONCAT(gallery.img_id) AS gallery FROM items LEFT JOIN gallery2 ON gallery2.item_id=items.id LEFT JOIN gallery ON gallery.item_id=items.id WHERE items.id = 1 GROUP BY items.id However this returns : id name gallery2 gallery 1 test 10,10,10,20,20,20,30,30,30 1,2,3,1,2,3,1,2,3 Could anyone help me with how to prevent the crossjoining?
LIMIT 5 Only Returns 2 When Using GROUP_CONCAT
I'm trying to limit my results in the following query. When I make this query without the GROUP_CONCAT lines, I get the number of results I specified in LIMIT, when I put those lines back in, I get less than the number of results I specify with LIMIT....
GROUP_CONCAT In Subquery - Error
HTML SELECT GROUP_CONCAT(F.name) FROM TABLEB F, TABLEC C WHERE F._id = C._id AND C.fieldC IN (1, 2, 3) I want to incorporate it into another query: HTML SELECT A.fieldA, SUM(A.fieldB) AS total, GROUP_CONCAT(A.fieldC) AS listing, (SELECT GROUP_CONCAT(F.name) FROM TABLEB F, TABLEC C WHERE F.i_id = C.i_id AND C.fieldC IN (listing) ) AS items FROM TABLEA A GROUP BY A.fieldA ORDER BY total But hat query throws the 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 '(SELECT GROUP_CONCAT(F.name) FROM TABLEB F, TABLEC C WHERE F.i_id at line 2
Count The Result Of Group_concat?
I learned group_concat from this forum, very useful command, but I am wondering if I can * COUNT the result of group_concat * for example, the following example, I know this is a wrong clause, but I hope I can COUNT and then use the result to do some HAVING works. So is it possible to count the result of group_concat? SELECT class, sex, COUNT(group_concat(name)) as num from school group by class, sex HAVING num > 20
4.0.x Equivalent/alternative For Group_Concat?
The following query works great on my test machine with mySQL 4.1.x. However, because cPanel is taking their sweet time working in mySQL 4.1, I can't use this on my website. Is there 'any' way to accomplish this same type of return in mySQL 4.0.22? Code:
Group_concat And String Functions
I am running: MySQL Distrib 5.0.15, for pc-linux-gnu on i686 I think I have run into a limitation of the GROUP_CONCAT function but I may really be doing things incorrectly. I hope you can point me in the right direction. We have a table of installed software that includes fields for: Product, Version, OperatingSystem and MachineName. Any software product can live on several different machines and might have different versions installed on each. I was hoping to produce a list of products, versions and an aggregate of the os/machine names that have this product installed. I tried: Code:
Select Group_concat Doesn't Return Numbers?!?
Say that I have a table with a with of integers and a row with text. For starters, I just want to have the integers returned in one string: select group_concat(MyIntegers) from MyTable; This gives me a lot of questionmarks: ????????? However, if I do this, I get the real result: select lower(group_concat(MyIntegers)) from MyTable; This gives me: 11,15,20,etc. If I select the string row, no extra work is needed. select group_concat(MyStrings) from MyTable; I get: "blabla,hey,there" etc. Why can't I get the numbers "as is" in the first query? I'm using the latest 5.0.19 Win32 MySQL server, running the latest ODBC driver etc. on a Windows server 2003.
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. =....
Make GROUP_CONCAT Not Group Null Values
I have a complex SELECT statement that uses GROUP_CONCAT to group like values -- which has been drastically over simplified below. From what I can surmise, GROUP_CONCAT is treating NULL as a value -- which I don't want. Is there a way to make it ignore null values to produce the 'desired output' below? TABLE: test +------------+------------+ | col_1 | col_2 | +------------+------------+ | NULL | a | +------------+------------+ | NULL | b | +------------+------------+ | 1 | c | +------------+------------+ | 1 | d | +------------+------------+ Current Output +------------+------------+ | col_1 | col_2 | +------------+------------+ | NULL | a|b | +------------+------------+ | 1 | c|d | +------------+------------+ Desired Output +------------+------------+ | col_1 | col_2 | +------------+------------+ | NULL | a | +------------+------------+ | NULL | b | +------------+------------+ | 1 | c|d | +------------+------------+
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
Mysql Function
i am hosting my website on a hosting website server.I made Mysql database there. I noticed one thing when i insert data in tables they have a colum called "function" with drop down box where they have values like ASCII,CHAR,SOUNDEX, UCASE, LCASE, COUNT,AVG any many other. I don't pick any of them. But I am just wondering if I need to pick either ASCII or char?
MYSQL Function For 'or'?
In the context of a where statement, what is the correct way to say 'or' as in the following: SELECT * FROM table WHERE field1 [or] field 2 LIKE "%$query%" This is for a basic search feature where people are tending to misspell words, I'd like to add a search words column that will also be searched.
Mysql 4 Function From A Mysql 5 Function
I found this function on the mysql website : CREATE FUNCTION substrCount(x TEXT, delim varchar(100)) RETURNS int RETURN (length(x) - length(REPLACE(x, delim, ''))) / length(delim); That works perfectly with mysql 5. But when I try to import this to mysql 4, I have an error : suse-server:/partages # mysql mybase <function-add.sql> output.tab ERROR 1064 (42000) at line 1: 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 '(x TEXT, delim varchar(100)) returns int return (length(x)-length(REPLACE(x, del' at line 1
Mysql Function
is there any function in mysql which tells you what the last row inserted into a table is?
|
TRACKER
