List:General Discussion« Previous MessageNext Message »
From:Visolve DB Team Date:November 6 2006 10:18am
Subject:Re: MAX + SUM in one query
View as plain text  
Hi

The query will work and should.

As of our understanding,
you want to retrieve the sum of the total column for a particular id, with 
its max price where the type='offer' and id same for both the tables.
Is it so?

Thanks
ViSolve DB Team.
----- Original Message ----- 
From: "Ahmad Al-Twaijiry" <ahmadt@stripped>
To: "Visolve DB Team" <mysql_support@stripped>
Cc: "MySQL List" <mysql@stripped>
Sent: Monday, November 06, 2006 2:33 PM
Subject: Re: MAX + SUM in one query


> but what if I want also to include another table
>
> for example, can I do this :
>
> SELECT R.Stock_StockID,S.StockName, MAX(R.RequestPrice),
> SUM(R.RequestTotal) FROM
> Request R, StocksT S WHERE  S.StockID=R.Stock_StockID AND
> R.RequestType='Offer'  AND
> R.RequestENDDate>=Date(now()) GROUP BY R.Stock_StockID
>
>
> Also remember I want to get the SUM of Total of the records that
> RequestPrice=MAX(RequestPrice)
>
> for example, if the MAX(RequestPrice)=200 then I just need the Total
> SUM of the records that there RequestPrice=200
>
> I don't need the SUM for all the records
>
>
> Thanks
>
>
> On 11/5/06, Visolve DB Team <mysql_support@stripped> wrote:
>> 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" <ahmadt@stripped>
>> To: "Visolve DB Team" <mysql_support@stripped>
>> Cc: "MySQL List" <mysql@stripped>
>> 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 <mysql_support@stripped> 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" <ahmadt@stripped>
>> >> To: "MySQL List" <mysql@stripped>
>> >> 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/mysql?unsub=1
>> >> >
>> >>
>> >>
>> >
>> >
>> > --
>> >
>> > Ahmad
>> > http://www.v-tadawul.com
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe:
>> > http://lists.mysql.com/mysql?unsub=1
>> >
>>
>>
>
>
> -- 
>
> Ahmad
> http://www.v-tadawul.com
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/mysql?unsub=1
> 

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