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