Different versions differnet results
I am getting two different results for the same set of commands on two different versions of MySQL. Can anyone explain what the issue is? CREATE TABLE `testdecimal` ( `rate` decimal(7,4) NOT NULL default '0.' ) ENGINE=MyISAM; INSERT INTO testdecimal SET rate=1468; select * from testdecimal; _ version 4.1.12 1468. version 5.0 999. Shantanu Oak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
First In First Out stock calculation query
Hi,
Here is the table in question.
drop table `portfolio1`;
CREATE TABLE `portfolio1` (
`ccode` varchar(99) default NULL,
`symbol` varchar(99) default NULL,
`transaction_date` date default NULL,
`rate` decimal(11,2) default NULL,
`quantity` int(11) default NULL,
`transaction` enum('buy','sell') default NULL
) TYPE=MyISAM;
INSERT INTO `portfolio1` VALUES ( 'soak', 'A', '2002-09-02', '345.92',
'350', 'buy');
INSERT INTO `portfolio1` VALUES ( 'soak', 'D', '2003-03-25', '39.90',
'1000', 'buy');
INSERT INTO `portfolio1` VALUES ( 'soak', 'A', '2002-12-05', '500.00',
'100', 'sell');
INSERT INTO `portfolio1` VALUES ( 'soak', 'A', '2003-12-05', '900.00',
'100', 'sell');
INSERT INTO `portfolio1` VALUES ( 'soak', 'D', '2004-12-05', '67.00',
'200', 'sell');
INSERT INTO `portfolio1` VALUES ( 'soak', 'D', '2004-12-09', '87.00',
'500', 'sell');
query I know...
SELECT
sub1.ccode,
sub1.symbol,
SUM(sub1.quantity_buy) buy,
SUM(sub1.quantity_sell) sell,
(SUM(sub1.quantity_buy) - SUM(sub1.quantity_sell)) stock,
MAX(sub1.transaction_date) transaction_date
FROM
(
SELECT ccode, symbol, quantity quantity_buy, 0 quantity_sell, transaction_date
FROM `portfolio1` p
WHERE transaction = 'buy'
union all
SELECT ccode, symbol, 0, quantity quantity_sell, ''
FROM `portfolio1` p
WHERE transaction = 'sell'
) sub1
GROUP BY
sub1.ccode, sub1.symbol;
The results are as per my expectations.
ccode symbol buy sell stock transaction_date
soak A 300 275 25 2003-09-02
soak D 900 400 500 2003-03-25
But I do also want to know the corresponding quantity bought on that
particular transaction_date.
ccode |symbol | buy | sell | stock | transaction_date | bought
soak | A | 300 | 275 | 25 | 2003-09-02 | 150
soak | D | 900 | 400 | 500 | 2003-03-25 | 300
and if it is possible, the second last entry if the stock > bought like this...
ccode |symbol | buy | sell | stock | transaction_date | bought
soak | A | 300 | 275 | 25 | 2003-09-02 | 150
soak | D | 900 | 400 | 500 | 2003-03-25 | 300
soak | D | 900 | 400 | 500 | 2002-02-25 | 100
soak | D | 900 | 400 | 500 | 2001-03-25 | 100
Note: even if 500 quantity was bought on 2001-03-25 I want to display only 100
in the bought column above because 400 units have already been sold off.
Thanks
Shantanu Oak
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
guess_table utility
Is there a free service where I can upload my CSV file to let the guess_table script go through and suggest create table statement? I did recently downloaded and tried the guess_table.pl script that is mentioned in Mysql cookbook. (10.36 Guessing Table Structure) I could not run the script due to some server incompatibility. Shantanu Oak [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
correcting the booklet
Dear Sir, I have written a paper on Mysql http://en.wikibooks.org/wiki/Programming:MySQL Will you please go through it and make corrections where ever necessary? Shantanu Oak [EMAIL PROTECTED] http://oksoft.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
average in Group By
Hi, I am trying to find average price of the shares in the portfolio table. I thought something like this should work... SELECT symbol, ((sum(buyrate*quantity))/quantity) as average from portfolio group by symbol; It does work, but wrong results. What is the correct query? Shantanu Oak __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL and Yahoo IM
Hi, I am using phpMyAdmin for last several years. I don't like it's "framed" design. Recently I read that I can query the database using my Yahoo IM. http://www.duncanlamb.com/sdba/?Projects/SQL+Admin I wonder if this software really works. Has anyone tried it before? What type of server will I need to host such software. (I apologize if it's out of topic) Shantanu Oak __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
