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 Complete Forum Thread with Replies
Related Forum Messages:
IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere! The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over £300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....
View Replies !
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 !
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 !
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 - 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 !
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 Replies !
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 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 !
Group By Count
I'm sorta having a small problem here I'm trying to do this query: SELECT count(*) as result ,tickets_name FROM tickets_tickets t group by tickets_name having count(*) > (avg of the count(*)); and that at the end is just explaining what i want since i haven't been able to get it right so far i tried tried to use two aggregates in a row but that gives me and error or placing a sub-query there.What i want in words would be to get the names and the count of the people that have placed more tickets than the avg. Thank ahead for any info.
View Replies !
Count And Group By ?
I'm working on a blogging site and the customer wants to display the "Top 5 Bloggers". The following two tables store the data and as you would guess the blog table stores the blog and blog_comments stores the comments. I can run 2 queries grouped by user_id to get the user count then write application code to process the results but would much prefer (for performance reasons) to handle this with SQL. Is there a pure SQL way to get a combined count of users from both tables? blog ------- blog_id title content user_id blog_comment ------------ blog_id comment_id content user_id
View Replies !
Count And Group
I'm working on a blogging site and one of the customer requirements is to display the "Top 5 Bloggers". Meaning a list of the 5 most active users. Following are the tables and as you would guess the BLOG table stores blog data and BLOG_COMMENT stores comments/replies to a blog. Question: Is there a pure SQL approach to get a collective count of users from both tables? I can achieve this with 2 queries then process the results with application code but would much rather do it with one SQL statement (if possible) blog -------- blog_id title content user_id blog_comment ------------- blog_id comment_id content user_id
View Replies !
Using Count(xxx) In Where While Using Group By
I have the following query which runs fine but the problem is when I try to only return results where COUNT(ET_ID)>2 I get the error "#1111 - Invalid use of group function ". Is there a different way to be able to do this? Current working query ------------------------------- SELECT COUNT(ET_ID) AS TotTickets, ET_InternalSection FROM ET WHERE ET_EventID=1837 AND ET_Status='Available' AND ET_PriceLevelID=2462 GROUP BY ET_InternalSection ORDER BY ET_InternalSection ASC New Query I am trying to run ------------------------------------ SELECT COUNT(ET_ID) AS TotTickets, ET_InternalSection FROM ET WHERE ET_EventID=1837 AND ET_Status='Available' AND ET_PriceLevelID=2462 AND COUNT(ET_ID)>2 GROUP BY ET_InternalSection ORDER BY ET_InternalSection ASC
View Replies !
Count On Group
Was wondering if comebody could help me out with a count that i am wanting to do , this is the query that i am doing and its working fine for getting the info that i want. $result = $DB->query( "SELECT name, UNIX_TIMESTAMP(date), AVG(speed) AS avg_speed, COUNT(*) AS number FROM readings group BY name ORDER BY avg_speed desc LIMIT $maxshow"); while(list($name, $date3, $speed, $count ) = mysql_fetch_row($result)) , what i also want to do is return a count of how many different hosts there are , i am returning the full count of all of them combined but i need something to reflect how many hosts , ie there is a count of 468 total tests taken but out of that there might be only say 30 different hosts that were tested.
View Replies !
Count And GROUP BY
I have worked out the following query and it tested fine using phpmyadmin.. SELECT uset, COUNT(*) FROM user WHERE society = '$society' GROUP BY uset The problem I have is displaying the results. In phpmyadmin the groups are displayed with the count of each, but I have no idea how I would display the results in php.
View Replies !
Using Group By Date And Count
Is there a way to select count(*) by grouping by date, and having multiple date ranges? combining... select field,count(*) from table where datefield > 2004/1/1 and datefield < 2004/1/31 and select field,count(*) from table where datefield > 2004/2/1 and datefield < 2004/2/29 so that the output is field 34 field 40
View Replies !
GROUP, UNION And COUNT...
writing a query to create a tree menu, it pulls from a table of music "tracks". In this database there are four "sub_genre" fields for each track, and I need to create a list of all used sub_genres and how many tracks are in each. The list will look like this: Expand|Select|Wrap|Line Numbers
View Replies !
Multiple COUNT() And GROUP BY
I think I'm on the right track, but I could use some help. I have a simple database with one table that contains observations of animal behavior. Columns include date, time, and animal_type. I want an output that shows the number of times each animal type was observed, grouped by date. Something like this (sorry about the formatting) date cat dog mouse 2004-1-1 1 2 3 2004-1-2 2 3 5 2004-1-3 1 2 3 I've gotten as far as getting one column, which would be this query: "select date, count(date) as cat from observations group by date" Can any one tell me how to get the rest?
View Replies !
Conditional COUNT() With GROUP BY
creating conditional COUNT statements. This is the query that works: SELECT COUNT(color='red' or NULL) as color_red FROM object; However I need to do a count based on more than one property like this: SELECT COUNT(color='red' AND state='solid') as red_solid_object FROM object;
View Replies !
Group By And Order By Using Count
I am having real problems with a query. I would usually solve this by doing one query and then another query based on the first to do the final sort (probably cheating!). I know how to do this in coldfusion, but not php. People have told me it is possible to do what i am after but none of their suggestions have worked so far (on another forum). Basically, I am grouping on a username and then counting the number of referrals that user has. I want to then order by, descending, the number of referrals, so the person with the most referrals comes at the top. Query syntax tried so far is here: SELECT user_1.username, Count(user.refid) AS CountOfrefid FROM [user] LEFT JOIN [user] AS user_1 ON user.refid = user_1.userid GROUP BY user_1.username order by Count(user.refid); and SELECT user_1.username, Count(user.refid) AS CountOfrefid FROM [user] LEFT JOIN [user] AS user_1 ON user.refid = user_1.userid GROUP BY user_1.username order by CountOfrefid; and SELECT user_1.username, Count(user.refid) AS CountOfrefid FROM [user] LEFT JOIN [user] AS user_1 ON user.refid = user_1.userid GROUP BY user_1.username order by 2; and SELECT distinct(user_1.username), Count(user.refid) AS CountOfrefid FROM [user] LEFT JOIN [user] AS user_1 ON user.refid = user_1.userid GROUP BY user_1.username order by 2;
View Replies !
Count Using Subquery/group By/etc?
I have a database witha table of jobs and a table of advertisers. Jobs can be assigned an advertiser from which they came, via the advertiser_id. So what I want to do is get my result list of advertisers with a count of how many jobs for each one. My advertisers table looks like this: CREATE TABLE `advertiser` ( `advertiser_id` mediumint(2) unsigned NOT NULL auto_increment, `advertiser_nicename` varchar(127) collate latin1_general_ci NOT NULL, `advertiser_name` varchar(127) collate latin1_general_ci NOT NULL, `advertiser_publisher` varchar(31) collate latin1_general_ci default NULL, PRIMARY KEY (`advertiser_id`) ); (I'm not going to normalize advertiser_publisher. :P ) And I'll just say that the jobs table looks like this for simplicity: CREATE TABLE `jobs` ( `job_id` int(10) unsigned NOT NULL auto_increment, `advertiser_id` mediumint(2) unsigned NULL, PRIMARY KEY (`job_id`) ); So I have my list of advertisers generated as such: PHP $advertisers = $DB->get_results( "SELECT * FROM `advertiser` ORDER BY `advertiser_publisher`, `advertiser_name` ASC" ); And I could be very silly and add a count such as this: PHP foreach ( $advertisers as $id => $advertiser ) { $advertisers[$id]->advertiser_jobs_count = $DB->get_results( "SELECT COUNT(advertiser_id) FROM `jobs` WHERE `advertiser_id` = $advertiser->advertiser_id", 'NUM', FALSE, TRUE ); } (Ok dont worry about all the php in there, i think its fairly self explanatory what is going on. ) So obviously I don't want to do that because its totally ridiculous and I'm sure that there would be an sql only method of doing it.
View Replies !
Group By And Count, Point
data in myTable (id) category point (1) 1 5 (3) 2 A (4) 2 3 (5) 2 1 (6) 1 B I have data in myTable like the above. The following codes produce the following results. code1 select sum(point) as point, count(*) as count from myTable where category=2 result1 point count 4 3 code2 select id, sum(point) as point, count(*) as count from myTable where category=2 group by id order by id desc result2 {id) point count (5) 1 1 I like to produce my target result below. target result (id) point count (5) 4 3
View Replies !
COUNT() Items In Group
I want to GROUP BY a field named 'type' in my database, and find out the total number of rows for each type. How do I achieve this in a single query? As an example of what I mean in case I didn't make it clear enough. Imagine this is the table... Code: TypeValue dogdata dogdata catdata dogdata catdata frogdata The query should return... dog = 3 cat = 2 frog = 1
View Replies !
COUNT(*) Problem With GROUP BY
What I'm trying todo is count the total amount of rows that match a series of tags that are related to one or more user_ids. I've noticed that the COUNT(*) operation returns the total amount of rows that were calculated for each group by label. so: lets say that a user_id of 2 belongs to users_tags both in a record where the tag_id is 1 and 2. SELECT user_id FROM users_tags WHERE tag_id IN (1,2) I'm therefore going to end up getting two results. SELECT user_id FROM users_tags WHERE tag_id IN (1,2) GROUP BY user_id. Ok now I have 1 record (that's what I want), but when I run the count operation I get: SELECT count(*) FROM users_tags WHERE tag_id IN (1,2) LIMIT 1 which gives me a value of two. Now lets say that user_id of 3 also belongs to both tags. then I'll get a count(*) of 4, but when I select columns I get two rows (which is the total count that I want).
View Replies !
Question About Select Count(*) With Group By
suppose a hypothetical table called 'table' with one field called 'field' of an arbitrary type. select count(*) from table where field='value' group by field produces no rows when run under the latest mysql. without "group by" it produces 1 row. is this according to the SQL standard or is this just a coincidence? in other words, can i rely on this behaviour to deduce that there are no fields with value 'value' in 'table'?
View Replies !
COUNT And GROUP BY In Forum System
I recently built my own forum system in PHP with a MySQL back end. It's been a real learning expererience and I have learned a lot of things. One issue I am having is with developing a top ten posters list to show the last 24 hours only.PHP Code: $topday_sql = "SELECT t1.fp_ID, t1.fp_posterID, t1.fp_time, "; $topday_sql .= "t2.u_ID, t2.u_contact "; $topday_sql .= "FROM $DBTABLE22 AS t1 "; $topday_sql .= "LEFT JOIN $DBTABLE11 AS t2 ON t1.fp_posterID=t2.u_ID "; $topday_sql .= "WHERE fp_time>='".(time()-86400)."' GROUP BY fp_posterID LIMIT 10"; $topday_result = @mysql_query($topday_sql, $DBlink); while ($topday_myrow = @mysql_fetch_array($topday_result)) { print $topday_myrow["u_contact"]." ".$totalposts."<br>"; Table one has has the post IDs, the poster ID, and the time of post. Table two has the Users ID and Users Name. Using the above statement I can get it to list 10 out of all of the users and if I remove LIMIT 10 all the users show up properly. Now I tried numerous things but I can't seem to do is get it to count how many entries there is per person. I was hoping some thing as simple as COUNT(t1.fp_ID) AS totalposts would of worked, but when the info displayes no value seems to return.
View Replies !
How To Get Count Of Rows Returned While Using Group & Having?
i'm using the having keyword to find certain set of rows and they are working properly. but what i want is , i want to count the total number of rows returned by this query using mysql. select name,count(date) from emp group by date having count(date)>3; this returned returns all the names that have more that 3 entries in the same date. it returned 5 rows and how can i get the count of rows(5) the query returned .
View Replies !
Select Group Order And Count All In One?
I have a column that looks a little like this: Code: 1 1 1 1 2 2 2 3 3 4 Not in order, but those kinds of numbers. I want to make a query that returns 1, 2, and 3 (in that order) because they have the most occurrences. I would also like it to return the number of occurrences. If it would go faster without the limit, I can stop the results with PHP just let me know
View Replies !
Count And Group By From Separate Table
I'm trying to make a query that will list a count of the number of sales a person has made in a particular area. My tables are as follows: sales (id, salesPersonName, location, salesStatus) sales_status (id, status) Both sales_status.status and sales.salesStatus are string values containing, eg, No Contact Made, Declined, Further Information, Sale Complete etc As an example: ........
View Replies !
COUNT And GROUP BY Display All Counted Rows
have a table as follows ID | ORG | Subject ----------------------- 100 | 14 | Some text 101 | 18 | Text1 102 | 18 | Text3 103 | 14 | Text4 104 | 18 | More text What query would I need to get the following output? (when I use COUNT(ID) and GROUP BY ORG it only returns 1 ID per ORG) ORG 14 - 2 results 100 103 ORG 18 - 3 results 101 102 104
View Replies !
Using A Single Sql Statement To Group And Count The Groups
I'm trying to count the total number of items in a table that are grouped e.g. my table is a shopping basket and is like this: basketID||orderID||productID||quantity 1||1||2||1 2||1||3||1 3||1||4||1 4||2||2||1 5||2||4||1 6||3||4||1 so product 4 appears 3 times, product 2 appears twice and product 3 appears just once how do use an sql statement (if it is possible) to group the products then list them in the order of which appears most so i could say SELECT * FROM basket GROUP BY productID; and that would group them for me, but i want them listed like productID 4 2 3 (as 4 has the most occurances, then 2, then 3)
View Replies !
How To Create Complex Query With Group By And Count
I have a table usertags: usertags ------------ - usertagid - usertagsiteid - usertagtext I want to select the usertagtext and count of usertagtext grouped by usertagtext. I only want it to show usertagtext that also have a match in the usertagsiteid to a given value for usertagtext. Not sure if that makes sense so here is an example: ......
View Replies !
Count And Group By Month In 1 Query For A Date Range?
I have a table that has employees work history on it. Basically the only thing I am worried about is the start and end date. My boss wants a report that charts out the quantity of workers for the past 12 months group by the month. For example Employee--Start Date--End Date Charles-----2008-6-3---2009-2-1 Vicky-------2009-1-1---Present So in this case there would be a quantity of 1 from June 08 to December 08. Then their would be a quantity of 2 from Jan 09 to Feb 09. And Vicky would also be counted for March since she is still working. I can do this by creating PHP functions and putting it all together, but I was just wondering if there was a way to do this all in 1 query.
View Replies !
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 Replies !
Group By Date, Count Of Multiple Fields Within Date
take table a: userID INT, userLogin VARCHAR and table b: customerID INT, userID INT, customerStarted DATE, customerFinished DATE what I'm looking to produce is a report by date, showing number of starts and finishes for all customers marked with a specific userID, like User 4: Date | starts | finishes 2008-10-01 | 0 | 5 2008-10-02 | 3 | 3 2008-10-03 | 4 | 2 2008-10-04 | 0 | 0 2008-10-05 | 3 | 3 ... etc.
View Replies !
Full Yearly Weekly Group By Group Report
I have table called tblTransactions: TblTransactions Group | Months | Year | Transaction A | 3 | 2007 | 45 B | 2 | 2007 | 89 A | 7 | 2007 | 50 A | 8 | 2007 | 34 B | 4 | 2007 | 55 A |12 | 2007 | 10 A | 1 | 2008 | 88 B | 3 | 2008 | 34 B | 5 | 2008 | 70 A | 5 | 2008 | 45 A | 9 | 2008 | 88 B | 7 | 2008 | 99 A |10 | 2008 | 77 A |11 | 2008 | 99 How is the T-SQL to make the result like this (the result will display all of the week (FULL), the target is I can compare week by week between years and group. A | 1 | 2007 | 0 B | 1 | 2007 | 0 A | 2 | 2007 | 0 B | 2 | 2007 | 89 A | 3 | 2007 | 45 B | 3 | 2007 | 0 A | 4 | 2007 | 0 B | 4 | 2007 | 55 A | 5 | 2007 | 0 B | 5 | 2007 | 0 A | 6 | 2007 | 0 B | 6 | 2007 | 0 A | 7 | 2007 | 50 B | 7 | 2007 | 0 A | 8 | 2007 | 34 B | 8 | 2007 | 0 A | 9 | 2007 | 0 B | 9 | 2007 | 0 A | 10| 2007 | 0 B | 10| 2007 | 0 A | 11| 2007 | 0 B | 11| 2007 | 0 A | 12| 2007 | 10 B | 12| 2007 | 0 A | 1 | 2008 | 88 B | 1 | 2008 | 0 A | 2 | 2008 | 0 B | 2 | 2008 | 0 A | 3 | 2008 | 0 B | 3 | 2008 | 34 A | 4 | 2008 | 0 B | 4 | 2008 | 0 A | 5 | 2008 | 45 B | 5 | 2008 | 70 A | 6 | 2008 | 0 B | 6 | 2008 | 0 A | 7 | 2008 | 0 B | 7 | 2008 | 99 A | 8 | 2008 | 0 B | 8 | 2008 | 0 A | 9 | 2008 | 88 B | 9 | 2008 | 0 A |10 | 2008 | 77 B |10 | 2008 | 0 A |11 | 2008 | 99 B |11 | 2008 | 0 A |12 | 2008 | 0 B |12 | 2008 | 0
View Replies !
Group By With Order BY, Or INSERT INTO (SELECT * GROUP BY)?
I seem to have a few duplicates entries in a very large database. My database has two identical tables. One is written to on a regular basis (every few seconds) and the other is used for queries, and updated from the write table on a daily basis. I'm trying to get rid of duplicate entries for the queries, and I thought the most effective way to do this would be to run the query as Expand|Select|Wrap|Line Numbers
View Replies !
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?
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 !
|
TRACKER
