List:General Discussion« Previous MessageNext Message »
From:Ahmad Al-Twaijiry Date:November 6 2006 12:02pm
Subject:Re: MAX + SUM in one query
View as plain text  
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 <mysql_support@stripped> 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" <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
> >
>
>


-- 
echo "Hello World :)"
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