caculate the percentage

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

caculate the percentage

JINHUI
Bonjour, How can I caculate the percentage of two column? --alter table ellipse_vegetation_together add column vegetation_fraction double; --update table ellipse_vegetation_together set vegetation_fraction = 'Sum'/total_area; what I want to get is the new column=>vegetation_fraction = 'Sum'/total_area; division->percentage Thanks a lot!!!
Reply | Threaded
Open this post in threaded view
|

Re: caculate the percentage

Gwendall Petit
Administrator
Hi,

You have to use the "UPDATE" keyword.

UPDATE ellipse_vegetation_together SET vegetation_fraction= Sum/total_area ...  ;
Note that maybe you will have a problem with your field "Sum" because it's a keyword of the SQL grammar.
Maybe you should rename it before.

Regards

Gwendall
Reply | Threaded
Open this post in threaded view
|

Re: caculate the percentage

JINHUI
Dear professor,

What I want to get finally is a percentage,can you give a guide?
I can get the result in decimal~

jinhui
Reply | Threaded
Open this post in threaded view
|

Re: caculate the percentage

Gwendall Petit
Administrator
Sorry but I don't understand your question.

I hope you know how to calculate a percentage ?
So you just have to adapt your SQL formula --> you can mix operations on field and constant value, such as 100.

Regards
Reply | Threaded
Open this post in threaded view
|

Re: caculate the percentage

JINHUI
Dear professor,

What I want to do it's use 34% to represent the vegetation fraction not 0.3366581815...,

I searched some SQL language, like:
update elps_veg set veg_Ftion=cast(cast(surf_veg/surf_elps)as decimal(16,4))*100 as varchar (15))+'%';
but not works

and how can I search and learn  the right SQL ?

Jinhui chen
Reply | Threaded
Open this post in threaded view
|

Re: caculate the percentage

Gwendall Petit
Administrator
If you want to write "34%" you have to steps :
1- calculate the numerique value --> the percentage
2- concatenate this value with a string value (%). For that, you have to use the || operator that allows to concatenate differents parameters (fields, text value, ...)
Update elps_veg set veg_Ftion= your formula || '%';
Regards

Gwendall
Reply | Threaded
Open this post in threaded view
|

Re: caculate the percentage

JINHUI
Good afternoon Prof.Gwendall,

I tried to use

update elps_veg set veg_Ftion=(surf_veg/surf_elps)*100|| '%';

but it shows that:

ERROR [Thread-27] (RunnableBackgroundJob.java:73) - Fatal error on the job named Executing script
org.gdms.data.types.IncompatibleTypesException: The field 'veg_Ftion' cannot be assigned: the expression cannot be implicitly cast from type 'string' to type 'double'

Regards

Jinhui
Reply | Threaded
Open this post in threaded view
|

Re: caculate the percentage

Gwendall Petit
Administrator
Yes it's normal. That's because your field "veg_Ftion" is a double ... and you're trying to add a string value in it.

So the instruction is only available if the type of your field is string.
ALTER TABLE elps_veg ADD COLUMN veg_Ftion_txt string;

UPDATE elps_veg SET veg_Ftion_txt = ((surf_veg/surf_elps)*100) || '%';
Regards

Gwendall
Reply | Threaded
Open this post in threaded view
|

Re: caculate the percentage

JINHUI
Yes,and exactly,I think if it can be 34% or 34.55%,it's perfect!!!rather than34.454564555445454%

and I try to find something about SQL function:

update elps_veg as select round('veg_Ftion',2);
or
select Floor(veg_Ftion) from elps_veg;
or
UPDATE elps_veg SET veg_Ftion_txt =cast (round(surf_veg/surf_elps)*100) )|| '%';

all that function I searched it's not good, Dear expert,come on or where I can study the details

Thanks

Jinhui