>I want a (very smart) sql query that wil check the data and see how
>it's possible to get SUM=100
SQL doesn't do recursion; sprocs do it, but slowly; so the job probably
belongs in the business logic or client layer.
PB
-----
Ahmad Al-Twaijiry wrote:
> 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
>>
>>
>
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.12.8/455 - Release Date: 9/22/2006
