Fw: Simple Query Question
Hi , count() function is a group function so use group by clause in your select statement if you are selecting more than one column data.. here is some sample query related to help you, select count(*) from trndisburse: output:1467 select count(*),pkdisburseid from trndisburse_TMP m Group by pkdisburseid; output: COUNT(*)PKDISBURSEID 100011120414090807001226 100011120414090807001228 100011120414090807001246 100011120414090807001252 100011120414090807001173 100011120414090807001187 100011120414090807001230 100011120414090807000859 10001112041409080742 10001112041409080751 10001112041409080797 100011120414090807001309 100011120414090807001314 100011120414090807001333 100011120414090807001290 10001112041409 080701 .. ..some more data... last equivlent to your problem: select count(pkdisburseid) from trndisburse_TMP m where grossamt>=6000 Group by pkdisburseid; select count(pkdisburseid),grossamt from trndisburse_TMP m where grossamt>=6000 Group by pkdisburseid,grossamt; Thanks , abhisehk choudhary www.tech4urhelp.blogspot.com From: Stefan Kuhn To: mysql@lists.mysql.com Sent: Saturday, 14 April 2012 4:51 PM Subject: Re: Simple Query Question On Saturday 14 April 2012 09:51:11 Willy Mularto wrote: > Hi, > Please help what is wrong with this simple query "SELECT COUNT(key_agent) > total FROM agents_consolidated WHERE total = 180" Thanks. You need to use having instead of where, see the documentation. Stefan > > > > Willy Mularto > F300HD+MR18DE (NLC1725) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Simple Query Question
Hi many thanks for the help :) On Apr 14, 2012, at 6:21 PM, Stefan Kuhn wrote: > On Saturday 14 April 2012 09:51:11 Willy Mularto wrote: >> Hi, >> Please help what is wrong with this simple query "SELECT COUNT(key_agent) >> total FROM agents_consolidated WHERE total = 180" Thanks. > You need to use having instead of where, see the documentation. > Stefan >> >> >> >> Willy Mularto >> F300HD+MR18DE (NLC1725) > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > Willy Mularto F300HD+MR18DE (NLC1725) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Simple Query Question
On Saturday 14 April 2012 09:51:11 Willy Mularto wrote: > Hi, > Please help what is wrong with this simple query "SELECT COUNT(key_agent) > total FROM agents_consolidated WHERE total = 180" Thanks. You need to use having instead of where, see the documentation. Stefan > > > > Willy Mularto > F300HD+MR18DE (NLC1725) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Simple Query Question
Hi, Please help what is wrong with this simple query "SELECT COUNT(key_agent) total FROM agents_consolidated WHERE total = 180" Thanks. Willy Mularto F300HD+MR18DE (NLC1725)
Re: Simple Query Question
Hi, Thanks, I just checked and it was a memcache that was caching the output. See I knew it was a simple solution ;) Thanks for the effort everyone and sorry for wasting time. Regards Ian 2009/12/17 Aleksandar Bradaric > Hi Ian, > > Why do you think something's wrong? Here is my test data and the results of > your query: > --- > mysql> SELECT * FROM wp_views; > +-+-++---+ > | blog_id | post_id | date | views | > +-+-++---+ > | 1 | 1 | 2009-12-16 | 2 | > | 1 | 1 | 2009-12-17 | 3 | > | 1 | 2 | 2009-12-16 | 4 | > | 1 | 2 | 2009-12-17 | 5 | > | 2 | 1 | 2009-12-16 | 6 | > | 2 | 1 | 2009-12-17 | 7 | > | 2 | 2 | 2009-12-16 | 8 | > | 2 | 2 | 2009-12-17 | 9 | > | 1 | 1 | 2009-12-18 | 1 | > | 1 | 2 | 2009-12-18 | 1 | > | 2 | 1 | 2009-12-18 | 1 | > | 2 | 2 | 2009-12-18 | 1 | > +-+-++---+ > 12 rows in set (0.00 sec) > > mysql> SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE > (date <= "2009-12-17" AND date >= "2009-12-10") GROUP BY blog_id, post_id > ORDER BY views DESC LIMIT 10; > +-+-+---+ > | blog_id | post_id | views | > +-+-+---+ > | 2 | 2 |17 | > | 2 | 1 |13 | > | 1 | 2 | 9 | > | 1 | 1 | 5 | > +-+-+---+ > 4 rows in set (0.00 sec) > --- > > Seems OK to me... Are you getting different results? > > > Take care, > Aleksandar > > > > Ian wrote: > >> Hi, >> >> I am sure there is a simple solution to this problem, I just cant find it >> :) >> >> I have got a table that records views for an article for each blog per >> day. >> So the structure is as follows: >> >> CREATE TABLE `wp_views` ( >> `blog_id` int(11) NOT NULL, >> `post_id` int(11) NOT NULL, >> `date` date NOT NULL, >> `views` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8; >> >> Now thats fine and I can pull top blogs per day and thats all fine, but >> what >> I am after is pulling the top articles for a time period and where I am >> running into problems is where two blogs have the same post_id's the views >> get sum()'d for the day and I cant figure out (read end of year mind >> block) >> how to get around it. Here is my current query (for last 7 days): >> >> SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date >> <= >> "2009-12-17" AND date >= "2009-12-10") GROUP BY blog_id, post_id ORDER BY >> views DESC LIMIT 10 >> >> Any ideas as to whats wrong. I know its something simple, I just cant put >> my >> finger on it. >> >> Thanks in advance, >> Ian >> >> >
Re: Simple Query Question
Hi Ian, Why do you think something's wrong? Here is my test data and the results of your query: --- mysql> SELECT * FROM wp_views; +-+-++---+ | blog_id | post_id | date | views | +-+-++---+ | 1 | 1 | 2009-12-16 | 2 | | 1 | 1 | 2009-12-17 | 3 | | 1 | 2 | 2009-12-16 | 4 | | 1 | 2 | 2009-12-17 | 5 | | 2 | 1 | 2009-12-16 | 6 | | 2 | 1 | 2009-12-17 | 7 | | 2 | 2 | 2009-12-16 | 8 | | 2 | 2 | 2009-12-17 | 9 | | 1 | 1 | 2009-12-18 | 1 | | 1 | 2 | 2009-12-18 | 1 | | 2 | 1 | 2009-12-18 | 1 | | 2 | 2 | 2009-12-18 | 1 | +-+-++---+ 12 rows in set (0.00 sec) mysql> SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date <= "2009-12-17" AND date >= "2009-12-10") GROUP BY blog_id, post_id ORDER BY views DESC LIMIT 10; +-+-+---+ | blog_id | post_id | views | +-+-+---+ | 2 | 2 |17 | | 2 | 1 |13 | | 1 | 2 | 9 | | 1 | 1 | 5 | +-+-+---+ 4 rows in set (0.00 sec) --- Seems OK to me... Are you getting different results? Take care, Aleksandar Ian wrote: Hi, I am sure there is a simple solution to this problem, I just cant find it :) I have got a table that records views for an article for each blog per day. So the structure is as follows: CREATE TABLE `wp_views` ( `blog_id` int(11) NOT NULL, `post_id` int(11) NOT NULL, `date` date NOT NULL, `views` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8; Now thats fine and I can pull top blogs per day and thats all fine, but what I am after is pulling the top articles for a time period and where I am running into problems is where two blogs have the same post_id's the views get sum()'d for the day and I cant figure out (read end of year mind block) how to get around it. Here is my current query (for last 7 days): SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date <= "2009-12-17" AND date >= "2009-12-10") GROUP BY blog_id, post_id ORDER BY views DESC LIMIT 10 Any ideas as to whats wrong. I know its something simple, I just cant put my finger on it. Thanks in advance, Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Simple Query Question
Hi, I am sure there is a simple solution to this problem, I just cant find it :) I have got a table that records views for an article for each blog per day. So the structure is as follows: CREATE TABLE `wp_views` ( `blog_id` int(11) NOT NULL, `post_id` int(11) NOT NULL, `date` date NOT NULL, `views` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8; Now thats fine and I can pull top blogs per day and thats all fine, but what I am after is pulling the top articles for a time period and where I am running into problems is where two blogs have the same post_id's the views get sum()'d for the day and I cant figure out (read end of year mind block) how to get around it. Here is my current query (for last 7 days): SELECT blog_id, post_id, sum( views ) AS views FROM wp_views WHERE (date <= "2009-12-17" AND date >= "2009-12-10") GROUP BY blog_id, post_id ORDER BY views DESC LIMIT 10 Any ideas as to whats wrong. I know its something simple, I just cant put my finger on it. Thanks in advance, Ian
Re: Simple query question
* John Mistler > I have a table in which the first column is either 1 or 0. The second > column is a number between 0 and 59. I need to perform a query > that returns > entries where: > > 1. IF the first column is 1, the second column is NOT 0 > 2. IF the first column is 0, the second column is anything. > > It seems simple, but I'm not getting it right. Any ideas? Try this: SELECT * FROM tab1 WHERE (col1 = 1 AND col2 <> 0) OR (col1 = 0) When combining AND and OR, proper use of parantheses is important. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple query question
I have a table in which the first column is either 1 or 0. The second column is a number between 0 and 59. I need to perform a query that returns entries where: 1. IF the first column is 1, the second column is NOT 0 2. IF the first column is 0, the second column is anything. It seems simple, but I'm not getting it right. Any ideas? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very simple query question
> i have a simple query > > select u.*,p.* from users u, profiles p > where u.uname = p.uname > and u.level != 0 > > Is there any tricks to make this use an index. If i do level=0 is uses an > index , but != does not. MySQL only uses an index if it will return less than approx. 30% of the records. It tries to guess this by looking at the cardinality of the index (the estimated number of different entries). In your case level=0 returned less than 30% of the records, so obviously level!=0 will return more than 70% of the records and MySQL desides to do a table scan (which is probably more efficient than first looking in the index and then searching for almost every single record). You can do a SELECT u.*,p.* FROM users AS u FORCE INDEX (u_level_index) JOIN profiles AS p USING (uname) WHERE u.level != 0 but this will only slow the query down. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
very simple query question
Hello i have a simple query select u.*,p.* from users u, profiles p where u.uname = p.uname and u.level != 0 Is there any tricks to make this use an index. If i do level=0 is uses an index , but != does not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: simple query question
See http://www.innodb.com Regards, Iikka ps. The MySQL manual also offers some rough guidelines on where InnoDB excels. On Fri, 13 Sep 2002, Meidling, Keith, CTR, OSD-C3I wrote: > I've seen a lot about 'InnoDB'. What is it? > > -Original Message- > From: Weaver, Walt [mailto:[EMAIL PROTECTED]] > Sent: Thursday, September 12, 2002 4:09 PM > To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] > Subject: RE: simple query question > > > I'd use the InnoDB table type and establish a primary key/foreign key > relationship (parent-child) between the two. That way referential integrity > will be done for you by the database. > > --Walt Weaver > Bozeman, Montana > > -Original Message- > From: Chris Burger [mailto:[EMAIL PROTECTED]] > Sent: Thursday, September 12, 2002 1:57 PM > To: [EMAIL PROTECTED] > Subject: simple query question > > > I have I hope a simple query question. > > > > I have two tables > > 1 One has customer id and a store id and transaction information > > 2 Second has a store id and store information > > > > There is multiple transactions in the first table, however there is > only one record for each store id in the second table. What I need to > make sure is that for every store id in the first table I have a > corresponding store id record in the second table? > > > > > Any suggestions would be appreciated. > > > > Chris Burger > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail ><[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: simple query question
I've seen a lot about 'InnoDB'. What is it? -Original Message- From: Weaver, Walt [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 4:09 PM To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: RE: simple query question I'd use the InnoDB table type and establish a primary key/foreign key relationship (parent-child) between the two. That way referential integrity will be done for you by the database. --Walt Weaver Bozeman, Montana -Original Message- From: Chris Burger [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 1:57 PM To: [EMAIL PROTECTED] Subject: simple query question I have I hope a simple query question. I have two tables 1 One has customer id and a store id and transaction information 2 Second has a store id and store information There is multiple transactions in the first table, however there is only one record for each store id in the second table. What I need to make sure is that for every store id in the first table I have a corresponding store id record in the second table? Any suggestions would be appreciated. Chris Burger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: simple query question
I'd use the InnoDB table type and establish a primary key/foreign key relationship (parent-child) between the two. That way referential integrity will be done for you by the database. --Walt Weaver Bozeman, Montana -Original Message- From: Chris Burger [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 1:57 PM To: [EMAIL PROTECTED] Subject: simple query question I have I hope a simple query question. I have two tables 1 One has customer id and a store id and transaction information 2 Second has a store id and store information There is multiple transactions in the first table, however there is only one record for each store id in the second table. What I need to make sure is that for every store id in the first table I have a corresponding store id record in the second table? Any suggestions would be appreciated. Chris Burger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
simple query question
I have I hope a simple query question. I have two tables 1 One has customer id and a store id and transaction information 2 Second has a store id and store information There is multiple transactions in the first table, however there is only one record for each store id in the second table. What I need to make sure is that for every store id in the first table I have a corresponding store id record in the second table? Any suggestions would be appreciated. Chris Burger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
