Re: MAX + SUM in one query
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"
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"
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 '-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 10Offer 2000 300
> now()
>2 10Offer 100 300
> now()
>3 10Offer 3010
> now()
>4 10Bid 210 100
> now()
>5 11Offer 3010
> now()
>6 10Offer 3010
> now()
>7 10Offer 5030
> 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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: when does auto_increment_increment and auto_increment_offset come into being?
Hi Obviously!. These variables were introduced in MySQL 5.0.2. These system variables accommodate multi-master replication with AUTO_INCREMENT. Thanks ViSolve DB Team. - Original Message - From: "Xueron Nee" <[EMAIL PROTECTED]> To: Sent: Monday, November 06, 2006 10:59 AM Subject: when does auto_increment_increment and auto_increment_offset come into being? Hi all, I am reading MySQL 5.1 Reference Manual and found about the two variables in Chapter 6. Replication. But I cannot find from when these two variables come into being. I am using 4.0.27 on my servers now, and can not find them by using "show variables". Should I upgrade my servers? Thanks! -- Xueron Nee <[EMAIL PROTECTED]> -- 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]
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"
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 '-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 10Offer 2000 300
> now()
>2 10Offer 100 300
> now()
>3 10Offer 3010
> now()
>4 10Bid 210 100
> now()
>5 11Offer 3010
> now()
>6 10Offer 3010
> now()
>7 10Offer 5030
> 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]
Re: MAX + SUM in one query
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"
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 '-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 10Offer 2000 300
now()
2 10Offer 100 300
now()
3 10Offer 3010
now()
4 10Bid 210 100
now()
5 11Offer 3010
now()
6 10Offer 3010
now()
7 10Offer 5030
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Convert hex to decimal?
Hey,
I have a table with a SMALLINT column that -- trough a mistake --
contains values like 57, 13 etc. which in fact are 0x57 and 0x13 (i.e.
HEX numbers).
How would I convert
13 ("false decimal") to 0x13 (Hex) and from there to 19 (decimal)?
I tried my luck with UNHEX and CAST but I only got 0 or NULL back respectively.
Thanks in advance
Fred
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
when does auto_increment_increment and auto_increment_offset come into being?
Hi all, I am reading MySQL 5.1 Reference Manual and found about the two variables in Chapter 6. Replication. But I cannot find from when these two variables come into being. I am using 4.0.27 on my servers now, and can not find them by using "show variables". Should I upgrade my servers? Thanks! -- Xueron Nee <[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transfering Databases
Rob Cochrane wrote: Hi All, I am a Web developer and have been using MySQL for a couple of years. I have just changed to a new development machine away from WinXP to Ubuntu Linux. My Dbase administration in the past has been via MySQL-Admin and SQLYog. I am now trying to bring all my databases into localhost and even under sudo MySQL-admin will not allow a restore! I am not a great command line expert, preferring a GUI. Using phpMyAdmin export/import gives a "#1046 - No database selected " error. I know I am doing something really stupid but I have been setting up this box for 5 days and had minimal sleep. Make sure you go into a database before trying to import it. You've done a single database dump which doesn't include a "use database" line at the top - so you need to select the database you want to import to before you start. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 '-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 10Offer 2000 300 now()
2 10Offer 100 300 now()
3 10Offer 3010 now()
4 10Bid 210 100 now()
5 11Offer 3010 now()
6 10Offer 3010 now()
7 10Offer 5030 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]
Re: Default Column Value
Hi On 2006-11-05 Bill Guion wrote: > In one of my tables I have a start_date - timestamp (2007-07-04). A > separate column, start_yr_mo, has 200704 (first seven characters of > timestamp without the '-'). Both are entered manually. Can I define > start_yr_mo as a default of, for example, set start_yr_mo = > concat(substr(start_date, 1, 4),substr(start_date,6,2)). Something > like You can use a "VIEW" for this: CREATE VIEW view_table (start_date, start_yr_mo) AS SELECT start, concat(substr(start_date, 1, 4),substr(start_date,6,2)) FROM orig_table ; mysql> SELECT * FROM view_table; +-+-+ | start_date | start_yr_mo | +-+-+ | 2007-07-04 | 200707 | +-+-+ 1 row in set (0.00 sec) Alternatively one could use a "TRIGGER" that updates the start_yr_mo whenever start_date is modified but I'm unsure if triggers are present before 5.1. Of course the best way could be to drop the start_yr_mo completely and let the application render it. Storing redundant data in a database is seldom a good idea and using SQL for formatting neither. bye, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Default Column Value
In one of my tables I have a start_date - timestamp (2007-07-04). A separate column, start_yr_mo, has 200704 (first seven characters of timestamp without the '-'). Both are entered manually. Can I define start_yr_mo as a default of, for example, set start_yr_mo = concat(substr(start_date, 1, 4),substr(start_date,6,2)). Something like ALTER TABLE events ALTER start_yr_mo start_yr_mo SET = concat(substr(start_date, 1, 4),substr(start_date,6,2)); -= Bill =- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transfering Databases
Rob, are you getting any error messages when trying to restore your MYSQL backups (I assume that you have backed up your databases on the XP system by using the MYSQL Administrator's backup function)? What are these messages? I also suppose that creating blank databases on the Linux system and directly copying the files of each database from the Windows system into their directories on Linux would do the trick (however, I have never tried it between heteregoneous systems - Windows versus Linux). HTH, Panos. On 11/5/06, Rob Cochrane <[EMAIL PROTECTED]> wrote: Hi All, I am a Web developer and have been using MySQL for a couple of years. I have just changed to a new development machine away from WinXP to Ubuntu Linux. My Dbase administration in the past has been via MySQL-Admin and SQLYog. I am now trying to bring all my databases into localhost and even under sudo MySQL-admin will not allow a restore! I am not a great command line expert, preferring a GUI. Using phpMyAdmin export/import gives a "#1046 - No database selected " error. I know I am doing something really stupid but I have been setting up this box for 5 days and had minimal sleep. Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Panos Tsapralis, Senior Software / Systems Engineer, +306948076902, Athens, GREECE.
Transfering Databases
Hi All, I am a Web developer and have been using MySQL for a couple of years. I have just changed to a new development machine away from WinXP to Ubuntu Linux. My Dbase administration in the past has been via MySQL-Admin and SQLYog. I am now trying to bring all my databases into localhost and even under sudo MySQL-admin will not allow a restore! I am not a great command line expert, preferring a GUI. Using phpMyAdmin export/import gives a "#1046 - No database selected " error. I know I am doing something really stupid but I have been setting up this box for 5 days and had minimal sleep. Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
