MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Ahmad Al-Twaijiry Date:November 6 2006 3:28am
Subject:MAX + SUM in one query
View as plain text  
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
Thread
MAX + SUM in one queryAhmad Al-Twaijiry6 Nov
  • Re: MAX + SUM in one queryVisolve DB Team6 Nov
    • Re: MAX + SUM in one queryAhmad Al-Twaijiry6 Nov
  • Re: MAX + SUM in one queryVisolve DB Team6 Nov
    • Re: MAX + SUM in one queryAhmad Al-Twaijiry6 Nov
  • Re: MAX + SUM in one queryVisolve DB Team6 Nov
    • Re: MAX + SUM in one queryAhmad Al-Twaijiry6 Nov
      • Re: MAX + SUM in one queryArjan Hulshoff6 Nov