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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
