Hi
That's fine.
But for the query, I have created a simple table which simulates as that of
yours. I have used simple domain names.
I typed the StockID as RequestID. Nothing morethan that. But it gives
solution for your query.
SELECT R.Stock_StockID,MAX(R.RequestPrice), SUM(R.RequestTotal) FROM
> Request R WHERE R.RequestType='Offer' AND
> R.RequestENDDate>=Date(now()) GROUP BY R.Stock_StockID
>
solution:
select StockID, sum(RequestTotal),max(RequestPrice) from test where
RequestType='offer' group by StockID;
Pls have a look into the table and the output for the query.
Thanks
ViSolve DB Team.
----- Original Message -----
From: "Ahmad Al-Twaijiry" <[EMAIL PROTECTED]>
To: "Visolve DB Team" <[EMAIL PROTECTED]>
Cc: "MySQL List" <mysql@lists.mysql.com>
Sent: Monday, November 06, 2006 12:10 PM
Subject: Re: MAX + SUM in one query
Hi
no R.RequestENDDate>=Date(now()) will work fine (I use it in other sql
queries)
also as you can see in my sql, I want to group using Stock_StockID ,
so your solution will not work with me
On 11/5/06, Visolve DB Team <[EMAIL PROTECTED]> wrote:
Hi,
The query what you tried will return empty set only, since you have
compared
the RequestENDDate with now(), which always returns false[due to
seconds].
Try extracting the date part alone from RequestENDDate for the Where
cond.
otherwise the query do well:
select RequestID, sum(RequestTotal),max(RequestPrice) from test where
RequestType='offer' group by RequestID;
Test table:
mysql> select * from t;
+------+--------+------+-------+---------------------+
| id | idtype | tot | price | d |
+------+--------+------+-------+---------------------+
| 10 | off | 200 | 14 | 2006-11-06 10:49:36 |
| 10 | off | 100 | 22 | 2006-11-06 10:49:36 |
| 10 | off | 120 | 4 | 2006-11-06 10:49:36 |
| 11 | off | 200 | 14 | 2006-11-06 10:49:36 |
| 11 | off | 120 | 4 | 2006-11-06 10:49:36 |
| 11 | bi | 120 | 4 | 2006-11-06 10:49:36 |
| 11 | bi | 120 | 4 | 2006-11-06 10:49:36 |
+------+--------+------+-------+---------------------+
output:
+------+----------+------------+
| id | sum(tot) | max(price) |
+------+----------+------------+
| 10 | 420 | 22 |
| 11 | 320 | 14 |
+------+----------+------------+
2 rows in set (0.01 sec)
Thanks,
ViSolve DB Team.
----- Original Message -----
From: "Ahmad Al-Twaijiry" <[EMAIL PROTECTED]>
To: "MySQL List" <mysql@lists.mysql.com>
Sent: Monday, November 06, 2006 8:58 AM
Subject: MAX + SUM in one query
> Hi everyone
>
> I have the following the table :
>
> CREATE TABLE `Request` (
> `RequestID` int(10) unsigned NOT NULL auto_increment,
> `Stock_StockID` int(10) unsigned NOT NULL default '0',
> `RequestType` enum('Bid','Offer') NOT NULL default 'Bid',
> `RequestTotal` int(10) unsigned NOT NULL default '0',
> `RequestPrice` float(10,2) NOT NULL default '1.00',
> `RequestENDDate` datetime NOT NULL default '0000-00-00 00:00:00',
> PRIMARY KEY (`RequestID`,`Customer_CustID`,`Stock_StockID`),
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
>
>
> The data in the table :
>
> RequestID Stock_StockID RequestType RequestTotal RequestPrice
> RequestENDDate
>
______________________________________________________________________________________
> 1 10 Offer 2000 300
> now()
> 2 10 Offer 100 300
> now()
> 3 10 Offer 30 10
> now()
> 4 10 Bid 210 100
> now()
> 5 11 Offer 30 10
> now()
> 6 10 Offer 30 10
> now()
> 7 10 Offer 50 30
> now()
>
>
> Now my question is how can I get the MAX(RequestPrice) and the
> SUM(RequestTotal) (WHERE MAX(RequestPrice) ) and where
> RequestType=Offer for each Stock_StockID
>
> I tried this
>
> SELECT R.Stock_StockID,MAX(R.RequestPrice), SUM(R.RequestTotal) FROM
> Request R WHERE R.RequestType='Offer' AND
> R.RequestENDDate>=Date(now()) GROUP BY R.Stock_StockID
>
> but it doesn't work.
>
> Anyone know how to do it ?
>
>
> Thanks
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
--
Ahmad
http://www.v-tadawul.com
--
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]
