This didn't work.
To explain in easy way, let say I have this table
ID, OrderID, Price, Total, Type
1 200 100 20 Offer
2 200 700 40 Offer
3 200 700 30 Offer
4 300 100 60 Offer
5 300 500 80 Offer
The result should be like this (when GROUP BY OrderID, I don't care
about the ID):
result record #1:
OrderID=200 MAX(Price)=700 SUM(Total)=70 <--- 70 from 40+30
result record #2
OrderID=300 MAX(Price)=500 SUM(Total)=80 <-- we have only one row
with the MAX(Price)=500 and OrderID=300 and the total or if is 80
Thanks
On 11/6/06, Visolve DB Team <[EMAIL PROTECTED]> wrote:
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]
>
--
echo "Hello World :)"
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
