List: | General Discussion | « Previous MessageNext Message » | |

From: | Ahmad Al-Twaijiry | Date: | September 25 2006 10:42am |

Subject: | Re: SUM in WHERE | ||

View as plain text |

Thanks Douglas, I know my question is very hard if my data was 22, 41, 10, 40, 30, 20, 201 ,22 ,20, 100 , 60 ,70 I want a (very smart) sql query that wil check the data and see how it's possible to get SUM=100 for example in the data above the **result** **can be** : 10,40,30,20 ====> 100 and no I don't try to get the row id=7 (this is very simple man, shame on me if I ask question like this). On 9/25/06, Douglas Sims <doug@stripped> wrote: > 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@stripped> > > > 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@stripped> 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@stripped> >> > >> > >> > >> 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@stripped> 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@stripped> >> >> > >> >> > >> >> > >> >> > >> >> > >> >> 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@stripped] > >> >> > Sent: Wednesday, 20 September 2006 2:24 a.m. > >> >> > To: Price, Randall > >> >> > Cc: Edward Macnaghten; mysql@stripped> >> >> > Subject: Re: SUM in WHERE > >> >> > > >> >> > > >> >> > Actually is this possible with simple SQL command in Mysql ? > >> >> > > >> >> > On 9/19/06, Price, Randall <randallp@stripped> 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@stripped> >> >> >> Phone: (540) 231-4396 > >> >> >> > >> >> >> -----Original Message----- > >> >> >> From: Ahmad Al-Twaijiry [mailto:ahmadt@stripped] > >> >> >> Sent: Tuesday, September 19, 2006 10:06 AM > >> >> >> To: Edward Macnaghten > >> >> >> Cc: mysql@stripped> >> >> >> 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@stripped> > 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@stripped> 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=1 > >> >> >> > >> >> >> > >> >> > > >> >> > > >> >> > -- > >> >> > > >> >> > 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@stripped> >> >> > 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. > >> >> > > >> >> > -- > >> >> > MySQL General Mailing List > >> >> > For list archives: http://lists.mysql.com/mysql > >> >> > To unsubscribe: http://lists.mysql.com/mysql? > >> >> unsub=DSims@stripped> >> >> > > >> >> > >> >> > >> > > >> > > >> > -- > >> > > >> > Ahmad Fahad AlTwaijiry > >> > > >> > -- > >> > MySQL General Mailing List > >> > For list archives: http://lists.mysql.com/mysql > >> > To unsubscribe: http://lists.mysql.com/mysql? > >> unsub=DSims@stripped> >> > >> > > > > > > -- > > > > Ahmad Fahad AlTwaijiry > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql?unsub=1 > > -- Ahmad Fahad AlTwaijiry

Thread | ||
---|---|---|

• SUM in WHERE | Ahmad Al-Twaijiry | 18 Sep |

• AW: SUM in WHERE | André Hänsel | 18 Sep |

• Re: SUM in WHERE | Ahmad Al-Twaijiry | 18 Sep |

• Re: SUM in WHERE | Edward Macnaghten | 18 Sep |

• Re: SUM in WHERE | Ahmad Al-Twaijiry | 19 Sep |

• Re: SUM in WHERE | Ahmad Al-Twaijiry | 19 Sep |

• RE: SUM in WHERE | Randall Price | 19 Sep |

• Re: SUM in WHERE | Ahmad Al-Twaijiry | 19 Sep |

• Re: SUM in WHERE | Felix Geerinckx | 21 Sep |

• Re: SUM in WHERE | Ahmad Al-Twaijiry | 24 Sep |

• RE: SUM in WHERE | Quentin Bennett | 19 Sep |

• Re: SUM in WHERE | Douglas Sims | 20 Sep |

• Re: SUM in WHERE | Ahmad Al-Twaijiry | 24 Sep |

• Re: SUM in WHERE | Douglas Sims | 24 Sep |

• Re: SUM in WHERE | Ahmad Al-Twaijiry | 24 Sep |

• Re: SUM in WHERE | Douglas Sims | 25 Sep |

• Re: SUM in WHERE | Ahmad Al-Twaijiry | 25 Sep |

• Re: SUM in WHERE | Peter Brawley | 25 Sep |

• adding columns to a large table | Helen M Hudson | 25 Sep |

• Re: adding columns to a large table | Ehrwin Mina | 25 Sep |

• RE: adding columns to a large table | Mugunthan SIFY | 25 Sep |

• RE: adding columns to a large table | Ow Mun Heng | 25 Sep |

• Re: adding columns to a large table | praj | 25 Sep |

• Re: adding columns to a large table | Helen M Hudson | 25 Sep |