List:General Discussion« Previous MessageNext Message »
From:Douglas Sims Date:September 24 2006 11:46pm
Subject:Re: SUM in WHERE
View as plain text  
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

Thread
SUM in WHEREAhmad Al-Twaijiry18 Sep
  • AW: SUM in WHEREAndré Hänsel18 Sep
    • Re: SUM in WHEREAhmad Al-Twaijiry18 Sep
  • Re: SUM in WHEREEdward Macnaghten18 Sep
    • Re: SUM in WHEREAhmad Al-Twaijiry19 Sep
      • Re: SUM in WHEREAhmad Al-Twaijiry19 Sep
        • RE: SUM in WHERERandall Price19 Sep
          • Re: SUM in WHEREAhmad Al-Twaijiry19 Sep
  • Re: SUM in WHEREFelix Geerinckx21 Sep
    • Re: SUM in WHEREAhmad Al-Twaijiry24 Sep
RE: SUM in WHEREQuentin Bennett19 Sep
  • Re: SUM in WHEREDouglas Sims20 Sep
    • Re: SUM in WHEREAhmad Al-Twaijiry24 Sep
      • Re: SUM in WHEREDouglas Sims24 Sep
        • Re: SUM in WHEREAhmad Al-Twaijiry24 Sep
          • Re: SUM in WHEREDouglas Sims25 Sep
            • Re: SUM in WHEREAhmad Al-Twaijiry25 Sep
              • Re: SUM in WHEREPeter Brawley25 Sep
  • adding columns to a large tableHelen M Hudson25 Sep
    • Re: adding columns to a large tableEhrwin Mina25 Sep
      • RE: adding columns to a large tableMugunthan SIFY25 Sep
        • RE: adding columns to a large tableOw Mun Heng25 Sep
    • Re: adding columns to a large tablepraj25 Sep
  • Re: adding columns to a large tableHelen M Hudson25 Sep