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" <[EMAIL PROTECTED]>
To: "Visolve DB Team" <[EMAIL PROTECTED]>
Cc: "MySQL List" <mysql@lists.mysql.com>
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 <[EMAIL PROTECTED]> 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" <[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]
>
--
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]
