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

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