doesn't work :( , tested with 4.1.21
On 9/20/06, Douglas Sims <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] 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
[EMAIL PROTECTED]
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:[EMAIL PROTECTED]
> Sent: Wednesday, 20 September 2006 2:24 a.m.
> To: Price, Randall
> Cc: Edward Macnaghten; mysql@lists.mysql.com
> Subject: Re: SUM in WHERE
>
>
> Actually is this possible with simple SQL command in Mysql ?
>
> On 9/19/06, Price, Randall <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
>> Phone: (540) 231-4396
>>
>> -----Original Message-----
>> From: Ahmad Al-Twaijiry [mailto:[EMAIL PROTECTED]
>> Sent: Tuesday, September 19, 2006 10:06 AM
>> To: Edward Macnaghten
>> Cc: mysql@lists.mysql.com
>> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
>>
>>
>
>
> --
>
> Ahmad Fahad AlTwaijiry
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> [EMAIL PROTECTED]
> 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/[EMAIL PROTECTED]
>
--
Ahmad Fahad AlTwaijiry
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
