Help building an alias field

[amosse]

Hello to everybody,

 

I am a beginner in php/mysql and have to build e query with calculated filed,

this is my problem:

 

I have this somple query,

select id,date_format(fin_record_date,'%d-%m-%y') as fin_record_date_it,fin_record_debit,fin_record_credit,(fin_record_debit-fin_record_credit) as fin_record_bal1 from fin_records;

 

Subsequently while fetching query I use php in order to calculate progressive balance in this way,

$fin_record_bal2 +=$row2[fin_record_bal1];

 

My question is: do you know one system in order to get same result in query statement without using PHP?

 

 

Thanks for your attention.

Amos

 

 

 

[kirk112]

Take a look at WITH ROLLUP function this might be what you are looking for

 

http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html

 

this will give you an extra row with all the values in the coloum added up.

 

If you do not need to loop through each record after the query has run you could use

 

SUM((fin_record_debit-fin_record_credit)) as fin_record_bal1

 

Hope this points you in the right direction

[amosse]

Thank you for your reply.

 

I checked but my problem is that I need to display all records,

I tried something like this:

 

select id,date_format(fin_record_date,'%d-%m-%y') as fin_record_date_it,fin_record_debit,fin_record_credit,(sum(fin_record_debit-fin_record_credit)) as fin_record_bal1 from fin_records group by fin_record_date with rollup

 

but is not working because I need to have o total (an addition row) for each row.

 

I would like to ask you another question:

How  can I import data in MySQL from an Open Office (DB) Table?

 

Many thanks.

Amos

 

 

[fenway]

You can use user variables for "row-based" counts/sums.  As for the import, I assume it's CSV-able, and mysql actually has such a table type (or LOAD DATA INFILE from any tab-separated file).

[amosse]

Thak you very much for your reply Mr Fenway,

 

I would like to ask you where can I find information or samples about user variables for "row-based" counts/sums method?

I checked on Mysql web site but I didn't find the right argument.

 

Many thanks.

 

 

[fenway]

Take a look here.

[amosse]

Thank you for your reply.

 

I red documentation as per your reply,

I tried to do this

 

select id,fin_record_debit,fin_record_credit,@bal1:=fin_record_debit-fin_record_credit as bal1,@bal2:=@bal2+(fin_record_debit-fin_record_credit) as balance from fin_records

but I get "NULL for balance column"

 

select id,fin_record_debit,fin_record_credit,@bal1:=fin_record_debit-fin_record_credit as bal1,@bal2:=@bal2+@bal1 into @balance from fin_records

but I get "The used SELECT statements have a different number of columns"

 

I don't, can you give some little help?

 

Many thanks.

Amos

 

 

 

 

[amosse]

Sorry

 

I have 5.0.22 essential MySQL version.

 

 

[fenway]

You haven't set the user variable first.

[amosse]

Do you mean like this:

set @bal1=fin_record_debit-fin_record_credit,@bal2=0;

select id,fin_record_debit,fin_record_credit,@bal2:=(@bal1:=+@bal2) from fin_records;

 

or like this:

set @bal1=fin_record_debit-fin_record_credit,@bal2=@bal1;

select id,fin_record_debit,fin_record_credit,@bal2:=(@bal1+@bal2) from fin_records;

 

it is steel NULL..

I don't know if you can give some more advice?

 

 

 

[amosse]

I tried (from Follow up on rolling sum - self join vs user variables) as well:

 

select id,fin_record_debit,fin_record_credit,(fin_record_debit-fin_record_credit) as bal1,

@runtot:=@runtot+bal1 as runtot from (select id,fin_record_debit,fin_record_credit,

(fin_record_debit-fin_record_credit) as bal1 from fin_records) as t

 

still NULL for runtot

 

 

 

 

[fenway]

You can't initialize it to a variable first!

[amosse]

I don't understand excuse me I am a beginner,

What is wrong in my previous example, could you be more explicit?

Could you help me a bit more?

 

Many thanks.

 

 

 

 

 

[fenway]

Try SET @runtot:=0, then select it on the next line.

[amosse]

Thank you for your reply.

 

I tried to do this:

SET @runtot:=0;

select id,@runtot from fin_records;

 

Still @runtot = NULL

 

I don't understand manual say "If you refer to a variable that has nor been inizialized, it has a value of NULL and a type of string"

now to inizialize a variable is SET statement?

 

I tried even

SET @runtot:=1;

select id,@runtot from fin_records;

 

Still @runtot = NULL

 

I don't understand, please tell me if the concept is right?

If it is, running sum will never work or not?

 

Thanks again

Amos

 

 

ps: I have 5.0.22 Windows Essentials (x86)

 

 

 

 

 

 

 

 

 

[fenway]

What do you get from this:

 

SET @t1=0, @t2=0, @t3=0;
SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

[amosse]

I get:

 

@t1:=(@t2:=1)+@t3:=4

 

 

 

[amosse]

Excuse me,previous message is incomplete:

 

I get

5,5,1,4

 

like manual : http://dev.mysql.com/doc/refman/5.1/en/user-variables.html

it seems to be ok...for mysql 5.1..

may be it needs to up date release from 5.0 to 5.1?

 

Thank you for your attention.

 

Amos

[fenway]

User variables have been around since 3.23...

[amosse]

Do you think can I do something in order to get result?

 

 

[fenway]

Just try a simple row counter first.

[amosse]

Sorry but really I don't understand...

You refered to manual (http://dev.mysql.com/doc/refman/5.1/en/user-variables.html) with the statement

set @t1=0,@t2=0,@t3=0;
SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3

that is working giving me the result:

5,5,1,4

 

In the same http page mentionned above you can find a post (the 1 st-one) of Joey Bartlett where is reporting the following statement:

set @pos=0;
select @pos:=@pos+1,name from players order by score desc;

 

Now if I take same sample above and I adapt to my table in this way:

set @pos=0;
select @pos:=@pos+1,id,fin_record_desc from fin_records order by id;

I get column @pos:=@pos+1 NULL

 

Please someone can tell me why the above statement is not working.....?

If the above sample is not working I assume that running sum will never work....

I would apreciate someone who can demostrate the contrary.

 

here the table as per the sample above:

DROP TABLE IF EXISTS `fin_records`;
CREATE TABLE `fin_records` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `fin_record_date` date NOT NULL default '0000-00-00',
  `id_bank_account` int(10) unsigned NOT NULL default '0',
  `id_fin_account` int(10) unsigned NOT NULL default '0',
  `fin_record_desc` varchar(45) NOT NULL default '',
  `fin_record_debit` double(9,2) unsigned NOT NULL default '0.00',
  `fin_record_credit` double(9,2) unsigned NOT NULL default '0.00',
  `fin_record_rec` tinyint(4) unsigned NOT NULL default '0',
  `id_production_unit` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `date_unit_account_fincode` (`fin_record_date`,`id_production_unit`,`id_fin_account`,`id_bank_account`,`fin_record_rec`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='cash & bank records; InnoDB free: 4096 kB; (`id_bank_account';

 

Ciao

 

 

 

 

 

 

 

 

[amosse]

Ok I got running sum:

here is the statement

select x1.id,x1.fin_record_date,x1.added,sum(x2.added) as runningtotal from
(select id,fin_record_date,count(*) as added from fin_records group by fin_record_date) as x1
inner join (select id,fin_record_date,count(*) as added from fin_records group by fin_record_date) as x2
on x1.id >= x2.id group by x1.fin_record_date;

 

Now I have to adapt it to my case.