RE: SUM in WHERE
This is a discussion on RE: SUM in WHERE within the MySQL General forum forums, part of the MySQL category; --> No, I don't think it is. I think you want to have a query that will return 'n' rows ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-27-2008, 10:18 PM
| ||||
| ||||
 RE: SUM in WHERE No, I don't think it is. I think you want to have a query that will return 'n' rows where the sum of Total is >= 100 If your table is ID Total 1 10 2 20 3 30 4 40 5 50 it would return 1 10 2 20 3 30 4 40 (sum total = 100) but if your table was ID Total 1 100 2 20 3 30 4 40 5 50 it would return 1 100 only. Have I got it right. Using only SQL, your best bet would be a stored procedure, otherwise its really application logic to select the rows one at a time and keep a running total. HTH Quentin -----Original Message----- From: Ahmad Al-Twaijiry [mailto:ahmadt@gmail.com] Sent: Wednesday, 20 September 2006 2:24 a.m. To: Price, Randall Cc: Edward Macnaghten; mysql@lists.mysql.com Subject: Re: SUM in WHERE Actually is this possible with simple SQL command in Mysql ? On 9/19/06, Price, Randall <randallp@vt.edu> wrote: > I tried it also with 5.0.24-community-nt and it still didn't work! > > Randall Price > > Microsoft Implementation Group > Secure Enterprise Computing Initiatives > Virginia Tech Information Technology > 1700 Pratt Drive > Blacksburg, VA 24060 > > Email: Randall.Price@vt.edu > Phone: (540) 231-4396 > > -----Original Message----- > From: Ahmad Al-Twaijiry [mailto:ahmadt@gmail.com] > Sent: Tuesday, September 19, 2006 10:06 AM > To: Edward Macnaghten > Cc: mysql@lists.mysql.com > Subject: Re: SUM in WHERE > > I tried it also with 4.1.21-log and still didn't work ! > > On 9/19/06, Ahmad Al-Twaijiry <ahmadt@gmail.com> wrote: > > I tried that before and it also doesn't work, is it because I'm using > > mysql version 4.1.19 ? > > > > On 9/19/06, Edward Macnaghten <eddy@edlsystems.com> wrote: > > > Ahmad Al-Twaijiry wrote: > > > > > > > Hi everyone > > > > > > > <snip> > > > > > > > SELECT * FROM tbl_name WHERE SUM(Total)=100 ORDER BY ID > > > > > > > > > > > > > > SELECT ID FROM tbl_name GROUP BY ID HAVING SUM(Total)=100 ORDER BY > ID > > > > > > > > > > > -- > > > > Ahmad Fahad AlTwaijiry > > > > > -- > > Ahmad Fahad AlTwaijiry > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=Randall.Price@vt.edu > > -- Ahmad Fahad AlTwaijiry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=q...infinity.co.nz The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated.      |
|
02-27-2008, 10:18 PM
| |||
| |||
| Re: SUM in WHERE Following is one way of doing what you want. mysql> show create table t; +------- +----------------------------------------------------------------------- -------------------------------------------------------------+ | Table | Create Table | +------- +----------------------------------------------------------------------- -------------------------------------------------------------+ | t | CREATE TABLE `t` ( `TransactionDate` datetime default NULL, `amount` float default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +------- +----------------------------------------------------------------------- -------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t; +---------------------+--------+ | TransactionDate | amount | +---------------------+--------+ | 2006-01-02 00:00:00 | 20 | | 2006-01-04 00:00:00 | 178 | | 2006-01-07 00:00:00 | 32.43 | | 2006-01-09 00:00:00 | 3 | | 2006-01-11 00:00:00 | -1000 | | 2006-01-15 00:00:00 | 33.9 | +---------------------+--------+ 6 rows in set (0.00 sec) mysql> set @total=0; Query OK, 0 rows affected (0.00 sec) mysql> select amount as amount1, tot as tot1 from (select amount, @total:=amount+@total as tot from t order by TransactionDate) AS Tx where Tot>100; +---------+------------------+ | amount1 | tot1 | +---------+------------------+ | 178 | 198 | | 32.43 | 230.430000305176 | | 3 | 233.430000305176 | +---------+------------------+ 3 rows in set (0.00 sec) Good luck! Douglas Sims Doug@Apley.com On Sep 19, 2006, at 4:02 PM, Quentin Bennett wrote: > No, I don't think it is. > > I think you want to have a query that will return 'n' rows where > the sum of Total is >= 100 > > If your table is > > ID Total > 1 10 > 2 20 > 3 30 > 4 40 > 5 50 > > it would return > > 1 10 > 2 20 > 3 30 > 4 40 > > (sum total = 100) > > but if your table was > > ID Total > 1 100 > 2 20 > 3 30 > 4 40 > 5 50 > > it would return > > 1 100 > > only. > > Have I got it right. > > Using only SQL, your best bet would be a stored procedure, > otherwise its really application logic to select the rows one at a > time and keep a running total. > > HTH > > Quentin > > -----Original Message----- > From: Ahmad Al-Twaijiry [mailto:ahmadt@gmail.com] > Sent: Wednesday, 20 September 2006 2:24 a.m. > To: Price, Randall > Cc: Edward Macnaghten; mysql@lists.mysql.com > Subject: Re: SUM in WHERE > > > Actually is this possible with simple SQL command in Mysql ? > > On 9/19/06, Price, Randall <randallp@vt.edu> wrote: >> I tried it also with 5.0.24-community-nt and it still didn't work! >> >> Randall Price >> >> Microsoft Implementation Group >> Secure Enterprise Computing Initiatives >> Virginia Tech Information Technology >> 1700 Pratt Drive >> Blacksburg, VA 24060 >> >> Email: Randall.Price@vt.edu >> Phone: (540) 231-4396 >> >> -----Original Message----- >> From: Ahmad Al-Twaijiry [mailto:ahmadt@gmail.com] >> Sent: Tuesday, September 19, 2006 10:06 AM >> To: Edward Macnaghten >> Cc: mysql@lists.mysql.com >> Subject: Re: SUM in WHERE >> >> I tried it also with 4.1.21-log and still didn't work ! >> >> On 9/19/06, Ahmad Al-Twaijiry <ahmadt@gmail.com> wrote: >>> I tried that before and it also doesn't work, is it because I'm >>> using >>> mysql version 4.1.19 ? >>> >>> On 9/19/06, Edward Macnaghten <eddy@edlsystems.com> wrote: >>>> Ahmad Al-Twaijiry wrote: >>>> >>>>> Hi everyone >>>>> >>>> <snip> >>>> >>>>> SELECT * FROM tbl_name WHERE SUM(Total)=100 ORDER BY ID >>>>> >>>>> >>>> >>>> SELECT ID FROM tbl_name GROUP BY ID HAVING SUM(Total)=100 ORDER BY >> ID >>>> >>> >>> >>> >>> -- >>> >>> Ahmad Fahad AlTwaijiry >>> >> >> >> -- >> >> Ahmad Fahad AlTwaijiry >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/mysql?unsub=Randall.Price@vt.edu >> >> > > > -- > > Ahmad Fahad AlTwaijiry > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql? > unsub=quentin.bennett@infinity.co.nz > The information contained in this email is privileged and > confidential and > intended for the addressee only. If you are not the intended > recipient, you > are asked to respect that confidentiality and not disclose, copy or > make use > of its contents. If received in error you are asked to destroy this > and contact the sender immediately. Your assistance is appreciated. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=DSims@apley.com > |
|
02-27-2008, 10:20 PM
| |||
| |||
| Re: SUM in WHERE doesn't work  , tested with 4.1.21On 9/20/06, Douglas Sims <doug@apley.com> wrote: > > Following is one way of doing what you want. > > mysql> show create table t; > +------- > +----------------------------------------------------------------------- > -------------------------------------------------------------+ > | Table | Create > Table > | > +------- > +----------------------------------------------------------------------- > -------------------------------------------------------------+ > | t | CREATE TABLE `t` ( > `TransactionDate` datetime default NULL, > `amount` float default NULL > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | > +------- > +----------------------------------------------------------------------- > -------------------------------------------------------------+ > 1 row in set (0.00 sec) > > mysql> select * from t; > +---------------------+--------+ > | TransactionDate | amount | > +---------------------+--------+ > | 2006-01-02 00:00:00 | 20 | > | 2006-01-04 00:00:00 | 178 | > | 2006-01-07 00:00:00 | 32.43 | > | 2006-01-09 00:00:00 | 3 | > | 2006-01-11 00:00:00 | -1000 | > | 2006-01-15 00:00:00 | 33.9 | > +---------------------+--------+ > 6 rows in set (0.00 sec) > > mysql> set @total=0; > Query OK, 0 rows affected (0.00 sec) > > mysql> select amount as amount1, tot as tot1 from (select amount, > @total:=amount+@total as tot from t order by TransactionDate) AS Tx > where Tot>100; > +---------+------------------+ > | amount1 | tot1 | > +---------+------------------+ > | 178 | 198 | > | 32.43 | 230.430000305176 | > | 3 | 233.430000305176 | > +---------+------------------+ > 3 rows in set (0.00 sec) > > > Good luck! > > Douglas Sims > Doug@Apley.com > > > > > > On Sep 19, 2006, at 4:02 PM, Quentin Bennett wrote: > > > No, I don't think it is. > > > > I think you want to have a query that will return 'n' rows where > > the sum of Total is >= 100 > > > > If your table is > > > > ID Total > > 1 10 > > 2 20 > > 3 30 > > 4 40 > > 5 50 > > > > it would return > > > > 1 10 > > 2 20 > > 3 30 > > 4 40 > > > > (sum total = 100) > > > > but if your table was > > > > ID Total > > 1 100 > > 2 20 > > 3 30 > > 4 40 > > 5 50 > > > > it would return > > > > 1 100 > > > > only. > > > > Have I got it right. > > > > Using only SQL, your best bet would be a stored procedure, > > otherwise its really application logic to select the rows one at a > > time and keep a running total. > > > > HTH > > > > Quentin > > > > -----Original Message----- > > From: Ahmad Al-Twaijiry [mailto:ahmadt@gmail.com] > > Sent: Wednesday, 20 September 2006 2:24 a.m. > > To: Price, Randall > > Cc: Edward Macnaghten; mysql@lists.mysql.com > > Subject: Re: SUM in WHERE > > > > > > Actually is this possible with simple SQL command in Mysql ? > > > > On 9/19/06, Price, Randall <randallp@vt.edu> wrote: > >> I tried it also with 5.0.24-community-nt and it still didn't work! > >> > >> Randall Price > >> > >> Microsoft Implementation Group > >> Secure Enterprise Computing Initiatives > >> Virginia Tech Information Technology > >> 1700 Pratt Drive > >> Blacksburg, VA 24060 > >> > >> Email: Randall.Price@vt.edu > >> Phone: (540) 231-4396 > >> > >> -----Original Message----- > >> From: Ahmad Al-Twaijiry [mailto:ahmadt@gmail.com] > >> Sent: Tuesday, September 19, 2006 10:06 AM > >> To: Edward Macnaghten > >> Cc: mysql@lists.mysql.com > >> Subject: Re: SUM in WHERE > >> > >> I tried it also with 4.1.21-log and still didn't work ! > >> > >> On 9/19/06, Ahmad Al-Twaijiry <ahmadt@gmail.com> wrote: > >>> I tried that before and it also doesn't work, is it because I'm > >>> using > >>> mysql version 4.1.19 ? > >>> > >>> On 9/19/06, Edward Macnaghten <eddy@edlsystems.com> wrote: > >>>> Ahmad Al-Twaijiry wrote: > >>>> > >>>>> Hi everyone > >>>>> > >>>> <snip> > >>>> > >>>>> SELECT * FROM tbl_name WHERE SUM(Total)=100 ORDER BY ID > >>>>> > >>>>> > >>>> > >>>> SELECT ID FROM tbl_name GROUP BY ID HAVING SUM(Total)=100 ORDER BY > >> ID > >>>> > >>> > >>> > >>> > >>> -- > >>> > >>> Ahmad Fahad AlTwaijiry > >>> > >> > >> > >> -- > >> > >> Ahmad Fahad AlTwaijiry > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: > >> http://lists.mysql.com/mysql?unsub=Randall.Price@vt.edu > >> > >> > > > > > > -- > > > > Ahmad Fahad AlTwaijiry > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql? > > unsub=quentin.bennett@infinity.co.nz > > The information contained in this email is privileged and > > confidential and > > intended for the addressee only. If you are not the intended > > recipient, you > > are asked to respect that confidentiality and not disclose, copy or > > make use > > of its contents. If received in error you are asked to destroy this > > and contact the sender immediately. Your assistance is appreciated. > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql?unsub=DSims@apley.com > > > > -- Ahmad Fahad AlTwaijiry |
|
02-27-2008, 10:20 PM
| |||
| |||
| Re: SUM in WHERE Hi Ahmad I tested that example query with version 5.0.19. According to the manual, (http://dev.mysql.com/doc/refman/5.0/en/ansi-diff- subqueries.html) derived tables (subqueries in the "from" clause) should work in versions 4.1.x and up, so I'm not sure why it didn't work for you. In most cases you can rewrite queries which use derived tables as queries with joins, but I think that would be very hard to do in this case. The key bit of logic in this doesn't actually require there to be a derived table. The inner query: SELECT amount, @total:=amount+@total AS tot FROM t ORDER BY TransactionDate will give you a result set with a running total, and then you can use whatever logic you need to give you the first one or more rows where @total exceeds the threshold (e.g. 100) (Be sure to initialize that @total variable before the SELECT) The easiest way to do this, of course, is as a subselect of another query but you could also do it in the perl/python/php/whatever layer which is sending this query to the database. Can you send a transcript of what you tried, including the "SHOW CREATE TABLE" statement? Douglas Sims Doug@Apley.com On Sep 24, 2006, at 10:09 AM, Ahmad Al-Twaijiry wrote: > doesn't work , tested with 4.1.21> > On 9/20/06, Douglas Sims <doug@apley.com> wrote: >> >> Following is one way of doing what you want. >> >> mysql> show create table t; >> +------- >> +-------------------------------------------------------------------- >> --- >> -------------------------------------------------------------+ >> | Table | Create >> Table >> | >> +------- >> +-------------------------------------------------------------------- >> --- >> -------------------------------------------------------------+ >> | t | CREATE TABLE `t` ( >> `TransactionDate` datetime default NULL, >> `amount` float default NULL >> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | >> +------- >> +-------------------------------------------------------------------- >> --- >> -------------------------------------------------------------+ >> 1 row in set (0.00 sec) >> >> mysql> select * from t; >> +---------------------+--------+ >> | TransactionDate | amount | >> +---------------------+--------+ >> | 2006-01-02 00:00:00 | 20 | >> | 2006-01-04 00:00:00 | 178 | >> | 2006-01-07 00:00:00 | 32.43 | >> | 2006-01-09 00:00:00 | 3 | >> | 2006-01-11 00:00:00 | -1000 | >> | 2006-01-15 00:00:00 | 33.9 | >> +---------------------+--------+ >> 6 rows in set (0.00 sec) >> >> mysql> set @total=0; >> Query OK, 0 rows affected (0.00 sec) >> >> mysql> select amount as amount1, tot as tot1 from (select amount, >> @total:=amount+@total as tot from t order by TransactionDate) AS Tx >> where Tot>100; >> +---------+------------------+ >> | amount1 | tot1 | >> +---------+------------------+ >> | 178 | 198 | >> | 32.43 | 230.430000305176 | >> | 3 | 233.430000305176 | >> +---------+------------------+ >> 3 rows in set (0.00 sec) >> >> >> Good luck! >> >> Douglas Sims >> Doug@Apley.com >> >> >> >> >> >> On Sep 19, 2006, at 4:02 PM, Quentin Bennett wrote: >> >> > No, I don't think it is. >> > >> > I think you want to have a query that will return 'n' rows where >> > the sum of Total is >= 100 >> > >> > If your table is >> > >> > ID Total >> > 1 10 >> > 2 20 >> > 3 30 >> > 4 40 >> > 5 50 >> > >> > it would return >> > >> > 1 10 >> > 2 20 >> > 3 30 >> > 4 40 >> > >> > (sum total = 100) >> > >> > but if your table was >> > >> > ID Total >> > 1 100 >> > 2 20 >> > 3 30 >> > 4 40 >> > 5 50 >> > >> > it would return >> > >> > 1 100 >> > >> > only. >> > >> > Have I got it right. >> > >> > Using only SQL, your best bet would be a stored procedure, >> > otherwise its really application logic to select the rows one at a >> > time and keep a running total. >> > >> > HTH >> > >> > Quentin >> > >> > -----Original Message----- >> > From: Ahmad Al-Twaijiry [mailto:ahmadt@gmail.com] >> > Sent: Wednesday, 20 September 2006 2:24 a.m. >> > To: Price, Randall >> > Cc: Edward Macnaghten; mysql@lists.mysql.com >> > Subject: Re: SUM in WHERE >> > >> > >> > Actually is this possible with simple SQL command in Mysql ? >> > >> > On 9/19/06, Price, Randall <randallp@vt.edu> wrote: >> >> I tried it also with 5.0.24-community-nt and it still didn't work! >> >> >> >> Randall Price >> >> >> >> Microsoft Implementation Group >> >> Secure Enterprise Computing Initiatives >> >> Virginia Tech Information Technology >> >> 1700 Pratt Drive >> >> Blacksburg, VA 24060 >> >> >> >> Email: Randall.Price@vt.edu >> >> Phone: (540) 231-4396 >> >> >> >> -----Original Message----- >> >> From: Ahmad Al-Twaijiry [mailto:ahmadt@gmail.com] >> >> Sent: Tuesday, September 19, 2006 10:06 AM >> >> To: Edward Macnaghten >> >> Cc: mysql@lists.mysql.com >> >> Subject: Re: SUM in WHERE >> >> >> >> I tried it also with 4.1.21-log and still didn't work ! >> >> >> >> On 9/19/06, Ahmad Al-Twaijiry <ahmadt@gmail.com> wrote: >> >>> I tried that before and it also doesn't work, is it because I'm >> >>> using >> >>> mysql version 4.1.19 ? >> >>> >> >>> On 9/19/06, Edward Macnaghten <eddy@edlsystems.com> wrote: >> >>>> Ahmad Al-Twaijiry wrote: >> >>>> >> >>>>> Hi everyone >> >>>>> >> >>>> <snip> >> >>>> >> >>>>> SELECT * FROM tbl_name WHERE SUM(Total)=100 ORDER BY ID >> >>>>> >> >>>>> >> >>>> >> >>>> SELECT ID FROM tbl_name GROUP BY ID HAVING SUM(Total)=100 >> ORDER BY >> >> ID >> >>>> >> >>> >> >>> >> >>> >> >>> -- >> >>> >> >>> Ahmad Fahad AlTwaijiry >> >>> >> >> >> >> >> >> -- >> >> >> >> Ahmad Fahad AlTwaijiry >> >> >> >> -- >> >> MySQL General Mailing List >> >> For list archives: http://lists.mysql.com/mysql >> >> To unsubscribe: >> >> http://lists.mysql.com/mysql?unsub=Randall.Price@vt.edu >> >> >> >> >> > >> > >> > -- >> > >> > Ahmad Fahad AlTwaijiry >> > >> > -- >> > MySQL General Mailing List >> > For list archives: http://lists.mysql.com/mysql >> > To unsubscribe: http://lists.mysql.com/mysql? >> > unsub=quentin.bennett@infinity.co.nz >> > The information contained in this email is privileged and >> > confidential and >> > intended for the addressee only. If you are not the intended >> > recipient, you >> > are asked to respect that confidentiality and not disclose, copy or >> > make use >> > of its contents. If received in error you are asked to destroy this >> > and contact the sender immediately. Your assistance is appreciated. >> > >> > -- >> > MySQL General Mailing List >> > For list archives: http://lists.mysql.com/mysql >> > To unsubscribe: http://lists.mysql.com/mysql? >> unsub=DSims@apley.com >> > >> >> > > > -- > > Ahmad Fahad AlTwaijiry > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=DSims@apley.com |
|
02-27-2008, 10:20 PM
| |||
| |||
| Re: SUM in WHERE Hi I need the result to be 100 not to more or less than 100 here is my query : mysql> select version() ; +------------+ | version() | +------------+ | 4.1.21-log | +------------+ 1 row in set (0.00 sec) my table : CREATE TABLE `tbl_name` ( `ID` int(11) NOT NULL auto_increment, `Total` int(11) NOT NULL default '0', PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 mysql> select * from tbl_name; +----+-------+ | ID | Total | +----+-------+ | 1 | 22 | | 2 | 41 | | 3 | 10 | | 4 | 40 | | 5 | 30 | | 6 | 20 | | 7 | 100 | | 8 | 100 | | 9 | 50 | | 10 | 50 | +----+-------+ 10 rows in set (0.31 sec) mysql> set @total=0; mysql> select Total as amount1, tot as tot1 from (select Total,@total:=Total+@total as tot from tbl_name order by ID) as Tx where Tot>100; I will get : +---------+------+ | amount1 | tot1 | +---------+------+ | 40 | 113 | | 30 | 143 | | 20 | 163 | | 100 | 263 | | 100 | 363 | | 50 | 413 | | 50 | 463 | +---------+------+ 7 rows in set (0.00 sec) but for =100 I will get mysql> set @total=0; Query OK, 0 rows affected (0.00 sec) mysql> select Total as amount1, tot as tot1 from (select Total,@total:=Total+@total as tot from tbl_name order by ID) as Tx where Tot=100; Empty set (0.00 sec) Thanks On 9/24/06, Douglas Sims <doug@apley.com> wrote: > Hi Ahmad > > I tested that example query with version 5.0.19. According to the > manual, (http://dev.mysql.com/doc/refman/5.0/en/ansi-diff- > subqueries.html) derived tables (subqueries in the "from" clause) > should work in versions 4.1.x and up, so I'm not sure why it didn't > work for you. > > In most cases you can rewrite queries which use derived tables as > queries with joins, but I think that would be very hard to do in this > case. > > The key bit of logic in this doesn't actually require there to be a > derived table. The inner query: > SELECT amount, @total:=amount+@total AS tot FROM t ORDER BY > TransactionDate > will give you a result set with a running total, and then you can > use whatever logic you need to give you the first one or more rows > where @total exceeds the threshold (e.g. 100) > (Be sure to initialize that @total variable before the SELECT) > > The easiest way to do this, of course, is as a subselect of another > query but you could also do it in the perl/python/php/whatever layer > which is sending this query to the database. > > Can you send a transcript of what you tried, including the "SHOW > CREATE TABLE" statement? > > > Douglas Sims > Doug@Apley.com > > > > On Sep 24, 2006, at 10:09 AM, Ahmad Al-Twaijiry wrote: > > > doesn't work , tested with 4.1.21> > > > On 9/20/06, Douglas Sims <doug@apley.com> wrote: > >> > >> Following is one way of doing what you want. > >> > >> mysql> show create table t; > >> +------- > >> +-------------------------------------------------------------------- > >> --- > >> -------------------------------------------------------------+ > >> | Table | Create > >> Table > >> | > >> +------- > >> +-------------------------------------------------------------------- > >> --- > >> -------------------------------------------------------------+ > >> | t | CREATE TABLE `t` ( > >> `TransactionDate` datetime default NULL, > >> `amount` float default NULL > >> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | > >> +------- > >> +-------------------------------------------------------------------- > >> --- > >> -------------------------------------------------------------+ > >> 1 row in set (0.00 sec) > >> > >> mysql> select * from t; > >> +---------------------+--------+ > >> | TransactionDate | amount | > >> +---------------------+--------+ > >> | 2006-01-02 00:00:00 | 20 | > >> | 2006-01-04 00:00:00 | 178 | > >> | 2006-01-07 00:00:00 | 32.43 | > >> | 2006-01-09 00:00:00 | 3 | > >> | 2006-01-11 00:00:00 | -1000 | > >> | 2006-01-15 00:00:00 | 33.9 | > >> +---------------------+--------+ > >> 6 rows in set (0.00 sec) > >> > >> mysql> set @total=0; > >> Query OK, 0 rows affected (0.00 sec) > >> > >> mysql> select amount as amount1, tot as tot1 from (select amount, > >> @total:=amount+@total as tot from t order by TransactionDate) AS Tx > >> where Tot>100; > >> +---------+------------------+ > >> | amount1 | tot1 | > >> +---------+------------------+ > >> | 178 | 198 | > >> | 32.43 | 230.430000305176 | > >> | 3 | 233.430000305176 | > >> +---------+------------------+ > >> 3 rows in set (0.00 sec) > >> > >> > >> Good luck! > >> > >> Douglas Sims > >> Doug@Apley.com > >> > >> > >> > >> > >> > >> On Sep 19, 2006, at 4:02 PM, Quentin Bennett wrote: > >> > >> > No, I don't think it is. > >> > > >> > I think you want to have a query that will return 'n' rows where > >> > the sum of Total is >= 100 > >> > > >> > If your table is > >> > > >> > ID Total > >> > 1 10 > >> > 2 20 > >> > 3 30 > >> > 4 40 > >> > 5 50 > >> > > >> > it would return > >> > > >> > 1 10 > >> > 2 20 > >> > 3 30 > >> > 4 40 > >> > > >> > (sum total = 100) > >> > > >> > but if your table was > >> > > >> > ID Total > >> > 1 100 > >> > 2 20 > >> > 3 30 > >> > 4 40 > >> > 5 50 > >> > > >> > it would return > >> > > >> > 1 100 > >> > > >> > only. > >> > > >> > Have I got it right. > >> > > >> > Using only SQL, your best bet would be a stored procedure, > >> > otherwise its really application logic to select the rows one at a > >> > time and keep a running total. > >> > > >> > HTH > >> > > >> > Quentin > >> > > >> > -----Original Message----- > >> > From: Ahmad Al-Twaijiry [mailto:ahmadt@gmail.com] > >> > Sent: Wednesday, 20 September 2006 2:24 a.m. > >> > To: Price, Randall > >> > Cc: Edward Macnaghten; mysql@lists.mysql.com > >> > Subject: Re: SUM in WHERE > >> > > >> > > >> > Actually is this possible with simple SQL command in Mysql ? > >> > > >> > On 9/19/06, Price, Randall <randallp@vt.edu> wrote: > >> >> I tried it also with 5.0.24-community-nt and it still didn't work! > >> >> > >> >> Randall Price > >> >> > >> >> Microsoft Implementation Group > >> >> Secure Enterprise Computing Initiatives > >> >> Virginia Tech Information Technology > >> >> 1700 Pratt Drive > >> >> Blacksburg, VA 24060 > >> >> > >> >> Email: Randall.Price@vt.edu > >> >> Phone: (540) 231-4396 > >> >> > >> >> -----Original Message----- > >> >> From: Ahmad Al-Twaijiry [mailto:ahmadt@gmail.com] > >> >> Sent: Tuesday, September 19, 2006 10:06 AM > >> >> To: Edward Macnaghten > >> >> Cc: mysql@lists.mysql.com > >> >> Subject: Re: SUM in WHERE > >> >> > >> >> I tried it also with 4.1.21-log and still didn't work ! > >> >> > >> >> On 9/19/06, Ahmad Al-Twaijiry <ahmadt@gmail.com> wrote: > >> >>> I tried that before and it also doesn't work, is it because I'm > >> >>> using > >> >>> mysql version 4.1.19 ? > >> >>> > >> >>> On 9/19/06, Edward Macnaghten <eddy@edlsystems.com> wrote: > >> >>>> Ahmad Al-Twaijiry wrote: > >> >>>> > >> >>>>> Hi everyone > >> >>>>> > >> >>>> <snip> > >> >>>> > >> >>>>> SELECT * FROM tbl_name WHERE SUM(Total)=100 ORDER BY ID > >> >>>>> > >> >>>>> > >> >>>> > >> >>>> SELECT ID FROM tbl_name GROUP BY ID HAVING SUM(Total)=100 > >> ORDER BY > >> >> ID > >> >>>> > >> >>> > >> >>> > >> >>> > >> >>> -- > >> >>> > >> >>> Ahmad Fahad AlTwaijiry > >> >>> > >> >> > >> >> > >> >> -- > >> >> > >> >> Ahmad Fahad AlTwaijiry > >> >> > >> >> -- > >> >> MySQL General Mailing List > >> >> For list archives: http://lists.mysql.com/mysql > >> >> To unsubscribe: > >> >> http://lists.mysql.com/mysql?unsub=Randall.Price@vt.edu > >> >> > >> >> > >> > > >> > > >> > -- > >> > > >> > Ahmad Fahad AlTwaijiry > >> > > >> > -- > >> > MySQL General Mailing List > >> > For list archives: http://lists.mysql.com/mysql > >> > To unsubscribe: http://lists.mysql.com/mysql? > >> > unsub=quentin.bennett@infinity.co.nz > >> > The information contained in this email is privileged and > >> > confidential and > >> > intended for the addressee only. If you are not the intended > >> > recipient, you > >> > are asked to respect that confidentiality and not disclose, copy or > >> > make use > >> > of its contents. If received in error you are asked to destroy this > >> > and contact the sender immediately. Your assistance is appreciated. > >> > > >> > -- > >> > MySQL General Mailing List > >> > For list archives: http://lists.mysql.com/mysql > >> > To unsubscribe: http://lists.mysql.com/mysql? > >> unsub=DSims@apley.com > >> > > >> > >> > > > > > > -- > > > > Ahmad Fahad AlTwaijiry > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql?unsub=DSims@apley.com > > -- Ahmad Fahad AlTwaijiry |
|
02-27-2008, 10:20 PM
| |||
| |||
| Re: SUM in WHERE Ahh... I think I understand what you are trying to do now. The query will keep the cumulative total of the "Total" column... so, your data is 22, 41, 10, 40, 30, 20... After the 22 the cumulative total is 22 After the 41 the cumulative total is 22+41 or 63 After the 10 the cumulative total is 63+10 or 73 After the 40 the cumulative total is 73+40 or 113 After the 30 the cumulative total is 113+30 or 143 ... So, you see, it is never exactly equal to 100 so the query doesn't return any rows. I think you are looking for the first row (ordered by the ID column) where the value is exactly 100. That would be the row with id=7. Here is a query which will give you that: SELECT * FROM tbl_name WHERE total=100 ORDER BY id LIMIT 1,1 Douglas Sims Doug@Apley.com On Sep 24, 2006, at 3:27 PM, Ahmad Al-Twaijiry wrote: > Hi > > I need the result to be 100 not to more or less than 100 > > here is my query : > > mysql> select version() ; > +------------+ > | version() | > +------------+ > | 4.1.21-log | > +------------+ > 1 row in set (0.00 sec) > > > my table : > CREATE TABLE `tbl_name` ( > `ID` int(11) NOT NULL auto_increment, > `Total` int(11) NOT NULL default '0', > PRIMARY KEY (`ID`) > ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 > > mysql> select * from tbl_name; > +----+-------+ > | ID | Total | > +----+-------+ > | 1 | 22 | > | 2 | 41 | > | 3 | 10 | > | 4 | 40 | > | 5 | 30 | > | 6 | 20 | > | 7 | 100 | > | 8 | 100 | > | 9 | 50 | > | 10 | 50 | > +----+-------+ > 10 rows in set (0.31 sec) > > mysql> set @total=0; > mysql> select Total as amount1, tot as tot1 from (select > Total,@total:=Total+@total as tot from tbl_name order by ID) as Tx > where Tot>100; > > I will get : > +---------+------+ > | amount1 | tot1 | > +---------+------+ > | 40 | 113 | > | 30 | 143 | > | 20 | 163 | > | 100 | 263 | > | 100 | 363 | > | 50 | 413 | > | 50 | 463 | > +---------+------+ > 7 rows in set (0.00 sec) > > > but for =100 I will get > > mysql> set @total=0; > Query OK, 0 rows affected (0.00 sec) > > mysql> select Total as amount1, tot as tot1 from (select > Total,@total:=Total+@total as tot from tbl_name order by ID) as Tx > where Tot=100; > Empty set (0.00 sec) > > > Thanks > > On 9/24/06, Douglas Sims <doug@apley.com> wrote: >> Hi Ahmad >> >> I tested that example query with version 5.0.19. According to the >> manual, (http://dev.mysql.com/doc/refman/5.0/en/ansi-diff- >> subqueries.html) derived tables (subqueries in the "from" clause) >> should work in versions 4.1.x and up, so I'm not sure why it didn't >> work for you. >> >> In most cases you can rewrite queries which use derived tables as >> queries with joins, but I think that would be very hard to do in this >> case. >> >> The key bit of logic in this doesn't actually require there to be a >> derived table. The inner query: >> SELECT amount, @total:=amount+@total AS tot FROM t ORDER BY >> TransactionDate >> will give you a result set with a running total, and then you can >> use whatever logic you need to give you the first one or more rows >> where @total exceeds the threshold (e.g. 100) >> (Be sure to initialize that @total variable before the SELECT) >> >> The easiest way to do this, of course, is as a subselect of another >> query but you could also do it in the perl/python/php/whatever layer >> which is sending this query to the database. >> >> Can you send a transcript of what you tried, including the "SHOW >> CREATE TABLE" statement? >> >> >> Douglas Sims >> Doug@Apley.com >> >> >> >> On Sep 24, 2006, at 10:09 AM, Ahmad Al-Twaijiry wrote: >> >> > doesn't work , tested with 4.1.21>> > >> > On 9/20/06, Douglas Sims <doug@apley.com> wrote: >> >> >> >> Following is one way of doing what you want. >> >> >> >> mysql> show create table t; >> >> +------- >> >> >> +-------------------------------------------------------------------- >> >> --- >> >> -------------------------------------------------------------+ >> >> | Table | Create >> >> Table >> >> | >> >> +------- >> >> >> +-------------------------------------------------------------------- >> >> --- >> >> -------------------------------------------------------------+ >> >> | t | CREATE TABLE `t` ( >> >> `TransactionDate` datetime default NULL, >> >> `amount` float default NULL >> >> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | >> >> +------- >> >> >> +-------------------------------------------------------------------- >> >> --- >> >> -------------------------------------------------------------+ >> >> 1 row in set (0.00 sec) >> >> >> >> mysql> select * from t; >> >> +---------------------+--------+ >> >> | TransactionDate | amount | >> >> +---------------------+--------+ >> >> | 2006-01-02 00:00:00 | 20 | >> >> | 2006-01-04 00:00:00 | 178 | >> >> | 2006-01-07 00:00:00 | 32.43 | >> >> | 2006-01-09 00:00:00 | 3 | >> >> | 2006-01-11 00:00:00 | -1000 | >> >> | 2006-01-15 00:00:00 | 33.9 | >> >> +---------------------+--------+ >> >> 6 rows in set (0.00 sec) >> >> >> >> mysql> set @total=0; >> >> Query OK, 0 rows affected (0.00 sec) >> >> >> >> mysql> select amount as amount1, tot as tot1 from (select amount, >> >> @total:=amount+@total as tot from t order by TransactionDate) >> AS Tx >> >> where Tot>100; >> >> +---------+------------------+ >> >> | amount1 | tot1 | >> >> +---------+------------------+ >> >> | 178 | 198 | >> >> | 32.43 | 230.430000305176 | >> >> | 3 | 233.430000305176 | >> >> +---------+------------------+ >> >> 3 rows in set (0.00 sec) >> >> >> >> >> >> Good luck! >> >> >> >> Douglas Sims >> >> Doug@Apley.com >> >> >> >> >> >> >> >> >> >> >> >> On Sep 19, 2006, at 4:02 PM, Quentin Bennett wrote: >> >> >> >> > No, I don't think it is. >> >> > >> >> > I think you want to have a query that will return 'n' rows where >> >> > the sum of Total is >= 100 >> >> > >> >> > If your table is >> >> > >> >> > ID Total >> >> > 1 10 >> >> > 2 20 >> >> > 3 30 >> >> > 4 40 >> >> > 5 50 >> >> > >> >> > it would return >> >> > >> >> > 1 10 >> >> > 2 20 >> >> > 3 30 >> >> > 4 40 >> >> > >> >> > (sum total = 100) >> >> > >> >> > but if your table was >> >> > >> >> > ID Total >> >> > 1 100 >> >> > 2 20 >> >> > 3 30 >> >> > 4 40 >> >> > 5 50 >> >> > >> >> > it would return >> >> > >> >> > 1 100 >> >> > >> >> > only. >> >> > >> >> > Have I got it right. >> >> > >> >> > Using only SQL, your best bet would be a stored procedure, >> >> > otherwise its really application logic to select the rows one >> at a >> >> > time and keep a running total. >> >> > >> >> > HTH >> >> > >> >> > Quentin >> >> > >> >> > -----Original Message----- >> >> > From: Ahmad Al-Twaijiry [mailto:ahmadt@gmail.com] >> >> > Sent: Wednesday, 20 September 2006 2:24 a.m. >> >> > To: Price, Randall >> >> > Cc: Edward Macnaghten; mysql@lists.mysql.com >> >> > Subject: Re: SUM in WHERE >> >> > >> >> > >> >> > Actually is this possible with simple SQL command in Mysql ? >> >> > >> >> > On 9/19/06, Price, Randall <randallp@vt.edu> wrote: >> >> >> I tried it also with 5.0.24-community-nt and it still didn't >> work! >> >> >> >> >> >> Randall Price >> >> >> >> >> >> Microsoft Implementation Group >> >> >> Secure Enterprise Computing Initiatives >> >> >> Virginia Tech Information Technology >> >> >> 1700 Pratt Drive >> >> >> Blacksburg, VA 24060 >> >> >> >> >> >> Email: Randall.Price@vt.edu >> >> >> Phone: (540) 231-4396 >> >> >> >> >> >> -----Original Message----- >> >> >> From: Ahmad Al-Twaijiry [mailto:ahmadt@gmail.com] >> >> >> Sent: Tuesday, September 19, 2006 10:06 AM >> >> >> To: Edward Macnaghten >> >> >> Cc: mysql@lists.mysql.com >> >> >> Subject: Re: SUM in WHERE >> >> >> >> >> >> I tried it also with 4.1.21-log and still didn't work ! >> >> >> >> >> >> On 9/19/06, Ahmad Al-Twaijiry <ahmadt@gmail.com> wrote: >> >> >>> I tried that before and it also doesn't work, is it because >> I'm >> >> >>> using >> >> >>> mysql version 4.1.19 ? >> >> >>> >> >> >>> On 9/19/06, Edward Macnaghten <eddy@edlsystems.com> wrote: >> >> >>>> Ahmad Al-Twaijiry wrote: >> >> >>>> >> >> >>>>> Hi everyone >> >> >>>>> >> >> >>>> <snip> >> >> >>>> >> >> >>>>> SELECT * FROM tbl_name WHERE SUM(Total)=100 ORDER BY ID >> >> >>>>> >> >> >>>>> >> >> >>>> >> >> >>>> SELECT ID FROM tbl_name GROUP BY ID HAVING SUM(Total)=100 >> >> ORDER BY >> >> >> ID >> >> >>>> >> >> >>> >> >> >>> >> >> >>> >> >> >>> -- >> >> >>> >> >> >>> Ahmad Fahad AlTwaijiry >> >> >>> >> >> >> >> >> >> >> >> >> -- >> >> >> >> >> >> Ahmad Fahad AlTwaijiry >> >> >> >> >> >> -- >> >> >> MySQL General Mailing List >> >> >> For list archives: http://lists.mysql.com/mysql >> >> >> To unsubscribe: >> >> >> http://lists.mysql.com/mysql?unsub=Randall.Price@vt.edu >> >> >> >> >> >> >> >> > >> >> > >> >> > -- >> >> > >> >> > Ahmad Fahad AlTwaijiry >> >> > >> >> > -- >> >> > MySQL General Mailing List >> >> > For list archives: http://lists.mysql.com/mysql >> >> > To unsubscribe: http://lists.mysql.com/mysql? >> >> > unsub=quentin.bennett@infinity.co.nz >> >> > The information contained in this email is privileged and >> >> > confidential and >> >> > intended for the addressee only. If you are not the intended >> >> > recipient, you >> >> > are asked to respect that confidentiality and not disclose, >> copy or >> >> > make use >> >> > of its contents. If received in error you are asked to >> destroy this >> >> > and contact the sender immediately. Your assistance is >> appreciated. >> >> > >> >> > -- >> >> > MySQL General Mailing List >> >> > For list archives: http://lists.mysql.com/mysql >> >> > To unsubscribe: http://lists.mysql.com/mysql? >> >> unsub=DSims@apley.com >> >> > >> >> >> >> >> > >> > >> > -- >> > >> > Ahmad Fahad AlTwaijiry >> > >> > -- >> > MySQL General Mailing List >> > For list archives: http://lists.mysql.com/mysql >> > To unsubscribe: http://lists.mysql.com/mysql? >> unsub=DSims@apley.com >> >> > > > -- > > Ahmad Fahad AlTwaijiry > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=DSims@apley.com |
|
02-27-2008, 10:20 PM
| |||
| |||
| adding columns to a large table I have a table with about 3million rows and I'm having trouble altering it to add 4 more columns. I'm using the MySQL Administrator and I've left it more than 24hours and its still not finished. I was going to try using a login to the console next, but is there a better way? Any advice much appreciated! Helen |
|
02-27-2008, 10:20 PM
| |||
| |||
| Re: adding columns to a large table At 12:53 PM 9/25/2006, Helen M Hudson wrote: >I have a table with about 3million rows and I'm having trouble >altering it to add 4 more columns. I'm using the MySQL >Administrator and I've left it more than 24hours and its still not >finished. I was going to try using a login to the console next, but >is there a better way? Any advice much appreciated! >Helen >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/mysql?unsub=emina@chikka.com > Hi Helen, I would like to know what type of engine your using in mysql? Is it mysisam or innodb? thank you, Ehrwin C. Mina |
|
02-27-2008, 10:20 PM
| |||
| |||
| RE: adding columns to a large table If you have enough disk space, create a temporary table with the four new columns. Insert into this table selecting records from the original table. Finally rename the tables. Hope this helps. Regards, S.Mugunthan -----Original Message----- From: Ehrwin Mina [mailto:ehrwin@chikka.com] Sent: Monday, September 25, 2006 12:35 PM To: Helen M Hudson; MySQL List Subject: Re: adding columns to a large table At 12:53 PM 9/25/2006, Helen M Hudson wrote: >I have a table with about 3million rows and I'm having trouble >altering it to add 4 more columns. I'm using the MySQL >Administrator and I've left it more than 24hours and its still not >finished. I was going to try using a login to the console next, but >is there a better way? Any advice much appreciated! >Helen >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe: http://lists.mysql.com/mysql?unsub=emina@chikka.com > Hi Helen, I would like to know what type of engine your using in mysql? Is it mysisam or innodb? thank you, Ehrwin C. Mina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=m...s@sifycorp.com ********** DISCLAIMER ********** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail & notify us immediately at admin@sifycorp.com Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com |
|
02-27-2008, 10:20 PM
| ||||
| ||||
| RE: adding columns to a large table On Mon, 2006-09-25 at 15:27 +0530, Mugunthan SIFY wrote: > If you have enough disk space, create a temporary table with the four new > columns. Insert into this table selecting records from the original table. > Finally rename the tables. isn't this resource intensive? Then again, since the OP did already wait 24 hours, that must've loaded the server as well. > Hope this helps. > > Regards, > S.Mugunthan > > -----Original Message----- > From: Ehrwin Mina [mailto:ehrwin@chikka.com] > Sent: Monday, September 25, 2006 12:35 PM > To: Helen M Hudson; MySQL List > Subject: Re: adding columns to a large table > > > At 12:53 PM 9/25/2006, Helen M Hudson wrote: > >I have a table with about 3million rows and I'm having trouble > >altering it to add 4 more columns. I'm using the MySQL > >Administrator and I've left it more than 24hours and its still not > >finished. I was going to try using a login to the console next, but > >is there a better way? Any advice much appreciated! > >Helen > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe: http://lists.mysql.com/mysql?unsub=emina@chikka.com > > > > Hi Helen, > > I would like to know what type of engine your using in mysql? > Is it mysisam or innodb? > > thank you, > > Ehrwin C. Mina > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=m...s@sifycorp.com > > > ********** DISCLAIMER ********** > Information contained and transmitted by this E-MAIL is proprietary to > Sify Limited and is intended for use only by the individual or entity to > which it is addressed, and may contain information that is privileged, > confidential or exempt from disclosure under applicable law. If this is a > forwarded message, the content of this E-MAIL may not have been sent with > the authority of the Company. If you are not the intended recipient, an > agent of the intended recipient or a person responsible for delivering the > information to the named recipient, you are notified that any use, > distribution, transmission, printing, copying or dissemination of this > information in any way or in any manner is strictly prohibited. If you have > received this communication in error, please delete this mail & notify us > immediately at admin@sifycorp.com > > > Watch the latest updates on Mumbai, with video coverage of news, events, > Bollywood, live darshan from Siddhivinayak temple and more, only on > www.mumbailive.in > > Watch the hottest videos from Bollywood, Fashion, News and more only on > www.sifymax.com > > |