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