Re: MAX + SUM in one query

[Visolve DB Team]

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]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]