Count() Function
I don't know why I cannot use the count function in mysql. I used the following query at the mysql prompt, but it gave me the syntax error at Count(*)
SELECT QProgram, COUNT(*) FROM ContactUs GROUP BY QProgram;
View Complete Forum Thread with Replies
Related Forum Messages:
Count Function
I have a table with 40 rows. After I connect to the server and select a database I try to store the number of rows in a certain variable using php. I'm using.... $count = mysql_query("SELECT count(*) FROM League1); echo $count; ...when I do this it displays "resourceID#2resourceID#4" For some reason this made me think I was getting an array instead of a straight up number so I added this line before echo $count; $count = count($count); ....and that just displayed "11" no matter how many rows there were in the table. After all of this I am getting the feeling that I am doing something totally wrong but everywhere I look to find out what I did wrong I get either an answer that is specific to another problem or something way too general.
View Replies !
COUNT(*) Function
SELECT COUNT(*) FROM (SELECT * FROM ORDERS WHERE GROUPID=34 AND BEGINTIME < UNIX_TIMESTAMP())a LEFT JOIN (SELECT * FROM ORDERS WHERE GROUPID=34 AND BEGINTIME < UNIX_TIMESTAMP())b ON a.PREFIX = b.PREFIX AND a.BEGINTIME <= b.BEGINTIME GROUP BY A.PREFIX, A.RATE HAVING COUNT(*) > 1; its giving the result of 10 rows: 2 4 5 2 1 5 6 4 3 5 but i want the total number of records that is "10" to calculate the correct indexing.
View Replies !
Count () Function
Cant anyone tell me why this very basic query won't process. This is to give a breakdown of Mem.heard 1-12 numbers ("where did you hear about us survey") for ea of the MOrig two user types EX: MOrig | heardof | count_heardof UserA 1 5 (times) UserA 2 6 (times) UserB 1 3 (times) UserB 2 8 (times) SELECT MOrig, heardof, count ( heardof ) as count_heardof FROM Mem GROUP BY MOrig, heardof ORDER BY MOrig
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 !
Count Untill Function
I am trying to create a leader board table which is created like so: User_ID, Week, Weekly_Points, Overall_Points I need to sort by overall points to find the users position in the overall leader board. This would be easy with a view but in mysql 4 I have been told they are not supported. What way should I go?
View Replies !
Count Function :: How Many Duplicates
I have a database which I need to query to find out how many duplicate rows have been entered and then to use that query to count how many times the same person has inputed the data twice. eg - It counts the duplicate data Then counts the amount of times that the name of the person appears in the results I can do the counting the duplicate data but how would I go about counting the reults returned from that query This is the query I am using to count the duplicates: SELECT COUNT(*) AS `Rows`, `Initials` FROM `input data` GROUP BY `Date`, `Time`, `Name of caller`, `Animal`, `Details`, `Telephone number`, `Method`, `Passed to`, `Initials`, `DDI` HAVING COUNT(*) > 1; How could I count the amount of times the same initials appear.
View Replies !
FUNCTION Does Not Exist (COUNT)
Im writing a servlet, which connects to my MySQL database via ConnectorJ - JDBC. This is my SQL String: SELECT BusinessUnit, COUNT (BusinessUnit) As Counter FROM Shortcuts JOIN BusinessUnit ON Shortcuts.BusinessUnit_BID = BusinessUnit.BID GROUP BY BusinessUnit_BID When i get phpmyadmin and go to "SQL" window, to just parse a SQL String, it works just fine. I get my table i want to. Now i do the same SQL string in my Servlet, i get this error message: MySQLSyntaxErrorException: FUNCTION DB.COUNT does not exist How can this be? Why is COUNT no function of JDBC/ConnectorJ/Java whatsoever? :( I searched google etc long to get an answer but still cant solve this problem. Adding "WHERE BusinessUnit IS NOT NULL " to my SQL Statement doesnt help. I tried to just make a COUNT statement, but it doesnt work either: Statement: "SELECT COUNT (*) AS Counter FROM Shortcuts" resolves this Error: MySQLSyntaxErrorException: 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 '*) AS Counter Why nothing work? =C Every other statements work just fine. Its just this **** COUNT what wont work so far..
View Replies !
Set A Limit On Count(*) Function?
I have no way to predict how many results the count(*) function is going to find. However, executing that function on a small set of results will work very fast, and executing it on a big set of results can take minutes or even more. Is there a way to tell MySQL not to keep counting if it finds, say 1000 rows?
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 !
Row Count Mismatch In Select Count(*) And Explain Select Count(*) From Table
mysql> explain select * from parameter; +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | parameter | ALL | NULL | NULL | NULL | NULL | 3354 | | +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> select count(*) from parameter; +----------+ | count(*) | +----------+ | 97 | +----------+ 1 row in set (0.00 sec)
View Replies !
#1136 - Column Count Doesn't Match Value Count At Row 1
I'm getting the above error with the following SQL Statement. I cannot seem to find the error in the code. The select statement does pull multiple rows. I'm using my SQL version: 4.1.19 Any clue on why this isn't working? INSERT INTO `Grants` ( `Project_Code` , `Grant_Code` , `Fiscal_Year` , `Capital` ) VALUES ( ( SELECT Project_Code, Grant_Code, FY, SUM( Capital ) FROM Grants_Temp WHERE Project_Code = 'OSUT' AND FY = '2006' AND Claim_Month = '072006' GROUP BY Grant_Code ORDER BY Grant_Code ) )
View Replies !
Trying To Get Total Count Plus Count From WHERE Clause In One Query
I am fairly familiar with mysql, have been using it with php for about a year now for my development work. One query I am stuck on is the following. I am doing reporting, and need to find the count from a WHERE clause compared to the total count for that group. For the sake of an example, let's say I have a prize table that contains a complete list of prizes. There is a column to list if the prize has been won or not (prizeUsed - Y or N) and another that has the prize name (prizeName - string). I want the final result to list something like: Total Prizes ____________________ 100 of 850 ......
View Replies !
Column Count Doesn't Match Value Count At Row 1
I am getting the following error when I run my query. Column count doesn't match value count at row 1 I have looked up this error and have checked and I appear to have the right number and names in my query. In the DB table I have 34 columns, and that is what I have in the query. The last 4 in the query are for the names of the images being uploaded, but am not sure how this all works, so I don't know if I need them, but have them there until I know for sure. Comment, Purchase and Remarks are not used in this form, but I have added them so everything is being shown in the query. Code:
View Replies !
Count(*) And ORDER By The Result Of Count
I want here to make a query which will reuse the result of a count(*) function. lets say my website does events at different places for the same date, and also at different dates: this makes a structure like this: Expand|Select|Wrap|Line Numbers
View Replies !
Using COUNT To Count Multiple Results
having a bit of trouble getting my head around how to set this query up. I've got a tracking script to monitor click-throughs for email campaigns I manage. It enters the users email address, ip, date etc. into a table each time they click off the email to the site. I've got a query that works just fine to display every click, but what I'd like is for it to just display each email address once, with the number of times they've clicked through next to the address. Here's the current query: ....
View Replies !
COUNT WHERE (COUNT > X OR ABOVE) And WHERE DATE -3 Days Before
Example of Date of request 1. SELECT Count(tbl_eventbooked.idEvent), Sum(tbl_eventmain.StartDateEvent) FROM tbl_eventmain Left Join tbl_eventbooked ON tbl_eventbooked.idEvent = tbl_eventmain.id_Event WHERE tbl_eventmain.StartDateEvent <= '-2' GROUP BY tbl_eventbooked.UserEmail, tbl_eventbooked.FirstName Example Count of request 2. SELECT Count(tbl_eventbooked.idEvent) FROM tbl_eventmain Left Join tbl_eventbooked ON tbl_eventbooked.idEvent = tbl_eventmain.id_Event WHERE Count(tbl_eventbooked.idEvent) >= '15' (but how do you tell it 50% of row X)
View Replies !
Count(*) As Count And TotalCount Of Count
data in myTable1 (n) country_id (1) 3 (2) 1 (41) 1 (5) 2 (6) 3 (7) 4 data in myTable4 (time) param (10 : 10) c=4 (10 : 12) c=2 (10 : 30) n=41&k=5 (10 : 35) c=1 (10 : 37) n=5 (10 : 50) c=2 (10 : 54) c=2 (10 : 55) n=1&cate=6 (11 : 12) c=2 (11 : 15) n=7 (11 : 20) c=1 I have data in myTables like the above. I have the following code. code (select left(time,2) as hour, count(*) as count from myTable4 where left(param,1)='c' and substring(param,3,1)=2 group by left(time,2) ) UNION all (select left(time,2) as hour, count(*) as count from myTable4,myTable1 where left(param,1)='n' and substring(substring_index(param, '&', 1) ,3)=myTable1.n and myTable1.country_id=2 group by left(time,2) ) order by hour And the code above produces the following result. result (day) count (10) 3 (10) 1 (11) 1 The following would-be code doesn't work correctly, but it will show what I want. would-be code (select left(time,2) as hour, count(*) as count, sum(count) as totalCount from myTable4 where left(param,1)='c' and substring(param,3,1)=2 group by left(time,2) ) UNION all (select left(time,2) as hour, count(*) as count, sum(count) as totalCount from myTable4,myTable1 where left(param,1)='n' and substring(substring_index(param, '&', 1) ,3)=myTable1.n and myTable1.country_id=2 group by left(time,2) ) order by hour And the following is my target result. target result (day) count totalCount (10) 3 5 (10) 1 5 (11) 1 5
View Replies !
Select Count Need Even 0 Count
I know I have seen the answer to this on here before (I think). I have a query that does a count and a join. I want to return records that are in one the one table and then their counts of how many times they have been clicked on, even if that count is 0. Here is what I have: Code: SELECT i.vin , count( i.vin ) AS clicks FROM inventory i RIGHT OUTER JOIN statistics s ON s.vin = i.vin WHERE i.active = 1 GROUP BY i.vin ORDER BY clicks ASC
View Replies !
How To Fix "Column Count Doesn't Match Value Count At Row 1" ?
I've been trying to install a portal to my installation of Invision Power Board, and I keep coming up with this problem. I post here for two reasons: firstly is that no one is responding over at the portal creator's thread and I need help, second is that this error looks like a mySQL error that could be solved without the need of the creator if possible. Here is the error returned: mySQL query error: INSERT INTO `ibf_portal_box` VALUES (1, 'lang.last_topics', '<box><menu>10,20,30,50</menu> <topics_show>10</topics_show> <order>last_post</order> <exforums></exforums> </box>', 0, 'middle', '*', 0, 4, 'last_topics', '1') mySQL error: Column count doesn't match value count at row 1 mySQL error code: Date: Tuesday 21st of December 2004 08:43:10 PM I am very new to mySQL and don't really understand it. What is wrong with the above that it is trying to insert, and how is it fixed?
View Replies !
Count(*) In A Select Returns "1". It Should Behave Like Select Count(*)
i'm trying to make a query work properly but I got lost: SELECT *,count(*) FROM cancons c, musics m, discos d, r_discos_cancons rdc WHERE c.c_id_music = m.m_id AND rdc.rdc_id_disc = d.d_id AND d.d_id_music = m.m_id AND m.m_id = 24 GROUP BY c_id note: cancons (ca) = songs (en) discos (ca) = cd's (en) music (ca) = musician (en) don't worry for the WHERE part. i need it because of the foreign keys. this query returns a table with the title of the song and some more data. on the right side, I get another column called "count(c_id)" with the number "1" in it for each row. That's supposed to be due to the "group by" clause, I think. I'd like to get the table with the songs, as usual, and, with the same query, I'd like to get the total number of rows selected.
View Replies !
Max Function
I have a table with name, priority and parent as three columns...I want to get the max(priority) grouped by the parent... I am trying to get name, priority, parent, max(parent) as the four columns in the result...Could some one help me with this select name, priority, parent from table1 select max(priority) from table1 group by parent are the two select statements that I wish to combine...is it possible..?
View Replies !
SQL IN() Function
so i have a query such as SELECT first_name FROM users WHERE user_ID IN (5,4,8,19,8,4) and i want the first_name to be retrieved for EACH of the cases (eg. name is repeated twice for 4 and 8)
View Replies !
Last Function
How do you get the last row in a grouped query. I have a table with running balances. At the end of the day, I want to find out the last balance. select * from accounts group by date(dateCol) This always gives me the first row of the grouped column dateCol.
View Replies !
Avg Function
Why doesn't this work: SELECT avg(value) as avg_value from playerstats where avg_value = '100' and stattype = 'r' group by avg_value I'm trying to select the avg_value of 'r' where the avg_value = '100'.
View Replies !
Using The AVG Function
I am trying to use this query: SELECT average(price) FROM `data` WHERE id IN ( '3', '3', '6' ) to average the price of items users select. The problem is that when a user selects a value more than once it only includes it in the average once. How can I make it average all the values?
View Replies !
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?
View Replies !
SOUNDEX Function
I'm using SOUNDEX mysql function to find similar sounding names from a table with 2 million distinct names. Unfortunately there is a single soundex code for every 200,000 names! Meaning there are only 200,000 distinct codes for 2 million entries. Is there any other function / library / technique to work around this immense 1:10 redundancy ? for example, soundex for 'avis', 'apex' and 'apps' is A120, but I'd like to differentiate between them in my search - meaning implement a "stricter" sound comparison than the soundex function offers.
View Replies !
MBREquals Function
Has the MBREquals function been implemented in the latest 4.1.1-alpha build. I am finding problems with that command. It says that problem is there in sql syntax. If it has been implemented. Can you please help me in the syntax of the command.I am finding the same problems with the Intersection,Union,difference,symdifferenc commands But manual says that they are not yet implemented. So I guess they are not yet implemented.
View Replies !
Equivalent Function
I use the following function in Oracle SELEC T decode(status,'A','Active','L','Active','Former') FROM Table What it means is: if status = A, return "Active", if status=L, return "Active", else return "Former" in the select statement. Decode in mysql has nothing to do with this functionality and I didnt see a function while browsing the docs online that did this. Is there an equivalent function in mySQL?
View Replies !
CONCAT Function
I'm needed to insert large BLOBs into a database. With the 1MB packet limit, sending larger amounts of data would be difficult, so I had a neat idea. I would do an initial insert of an empty record and get the auto_insert ID from the response, and then loop through, appending data to the record. My table is simple. One unsigned int auto_increment field (DataID), and one long blob field (BinaryData). When I loop through the data to send, I run: UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data here') WHERE DataID = 35 The binary data I insert I escape null characters, backslashes, single and double quotes. The data seems to insert fine. The problem is that as I increase the amount of data in the field, CONCAT seems to drop all but the last 416k of the data. Thus if I loop through adding 400k blocks at a time (Which I do) I am left with at most 800k of data in the blob field. Using 4k blocks I end up with 419k of data in the field when all is said and done. Please let me know if/when this will be fixed, and if there is a work around that might be used, r a better way to insert BLOB data is known.
View Replies !
CONCAT Function (bug?)
I'm needed to insert large BLOBs into a database. With the 1MB packet limit, sending larger amounts of data would be difficult, so I had a neat idea. I would do an initial insert of an empty record and get the auto_insert ID from the response, and then loop through, appending data to the record. My table is simple. One unsigned int auto_increment field (DataID), and one long blob field (BinaryData). When I loop through the data to send, I run: UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data here') WHERE DataID = 35 The binary data I insert I escape null characters, backslashes, single and double quotes. The data seems to insert fine. The problem is that as I increase the amount of data in the field, CONCAT seems to drop all but the last 416k of the data. Thus if I loop through adding 400k blocks at a time (Which I do) I am left with at most 800k of data in the blob field. Using 4k blocks I end up with 419k of data in the field when all is said and done.
View Replies !
A Date Function
I read that MySQL needs dates to be in the form YYYY-MM-DD before you can store them in a table. Since users probably won't enter dates in that order (more likely MM-DD-YYYY or MM/DD/YYYY) is there a MySQL date function that I can apply to the date before it is inserted into the database to format the date correctly? (or does the order of the elements have to be taken care of by the application prior to the database query?)
View Replies !
Add_date Function
I am running the query: SELECT DATE_ADD('pubdate', INTERVAL 3 YEAR) as new_date, pub_autokey from pubs but it returns a null value. What am I doing wrong? In the bigger picture, I want to take a date from pubdate column, add 3 years to it, then save that return value in a different column named pulldate. I am unsure how to do that - can I nest select and insert statements? Something like: INSERT into pubs set pulldate='(SELECT DATE_ADD('pubdate', INTERVAL 3 YEAR) as new_date, pub_autokey from pubs)'
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 !
Date Function?
I know there is a date function : TO_DAYS but is there a function that will turn a date object into millseconds or seconds?
View Replies !
Dateadd Function
i have a question what do you mean by this:YMD(YEARS(dateadd(month,0,'2006-10-11')),month(dateadd(month,0,'2006-10-11')),lastday(dateadd(month,0,'2006-10-11')))) can somebody explains to me what do this means, is this same as dateadd(month,0,date)?
View Replies !
Preg_replace Function
I am looking for a preg_replace function in mysql....one where I can match a substring within a body of text and replace it with a new substring. I have been looking at both the replace and match functions that mysql offers, but nothing seems to fit the bill. Can someone help me construct a simple query so I can update some text within a field without having to write a php script to do so?
View Replies !
ISNULL Function
In MS SQL I often use the ISNULL function in the select statement to return the non-null field. For example, isnull(field1,field2) would return field1 if there is a value in it but if it is NULL it would return field2. I am now trying to do something similar in MySQL but I see that ISNULL does not work. Is there another easy way to do this in a select statement in MySQL?
View Replies !
LIKE In Search Function
my mysql server experiences really high load average and this is function which is rans most, does anyone know how to rewrite optimize this? Would be appreciated , thanks. if ($q) { $exp = explode(" ",$q); for ($i=0; $i<count($exp); $i++) { $hvahvor .= " && title LIKE '%$exp[$i]%'"; } } $this->get = mysql_query("SELECT id,type,title,url,sname,surl,date FROM $this->mysql_tb_dl WHERE $hvahvor ORDER BY id DESC LIMIT $this->page, $this->limit");
View Replies !
Lookup Function
i have 3 tables; Customer, DVDDetails and OrderForm. In the Customer table there are a few standard fields, including a Customer_ID field set to auto increment and as the Primary Key.In my OrderForm table, i have fields into which data about the order is inputted, but i also have a Customer_ID field.Is it possible to have MySQL 'lookup' the Customer_ID field as assigned in the Customer table and basically paste this number into the Customer_ID field in the OrderForm table?I basically want to be able to associate the Customer's details with their order by the unique order number. the Customer_ID fields are the only 2 common fields between these 2 tables...an easier way to this?
View Replies !
Function For 2 Dates
i have inserted 2 dates say "2008-01-01" and "2008-01-05", I want the return to be "2008-01-01", "2008-01-02", "2008-01-03", "2008-01-04", "2008-01-05" . Is there a mysql function or need a custom function? IF so, can you provide some reference ?
View Replies !
Median Function
Is there function to find the median value of a column? To all of you who don't remember your core statistics anymore: a median is NOT an average. 1, 1, 1, 10... average = 3.25 1, 1, 1, 10... median = 1
View Replies !
Rank Function ?
looking for an aggregate function similar to count to return the rank of a grouped set. What I mean by rank is the relationship of the count of the group to the total group. An example would be: SELECT COUNT(*) AS Rows, RANK, Manufacturer FROM Cars GROUP BY Manufacturer If the result was Rows Rank Manufacturer 10 2 Audi 20 1 Honda 5 3 Kia I would like to be able to order by any column but still have the rank mean something.
View Replies !
|
TRACKER
