List:General Discussion« Previous MessageNext Message »
From:Ahmad Al-Twaijiry Date:September 24 2006 8:27pm
Subject:Re: SUM in WHERE
View as plain text  
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=1
>
>


-- 

Ahmad Fahad AlTwaijiry
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