Mysql Calculate total memory based on prefix -

have table:

 create table if not exists `assets` (   `asset_id` bigint(20) not null auto_increment,   `asset_memory` varchar(255) not null,   `asset_memory_prefix` tinyint(1) not null default '2',   primary key (`asset_id`),   key `locations` (`position_id`) ) engine=myisam  default charset=utf8 auto_increment=2 ; 

i need calculate sum of memory of specific asset_id's based on asset_memory , asset_memory_prefix asset_memory_prefix defined this:

    $_prefix = array(     array("id" => 0, "prefix" => "b", "prefix_name" => "byte", "pow_value" => "0"),     array("id" => 1, "prefix" => "kb", "prefix_name" => "kilobyte", "pow_value" => "1"),     array("id" => 2, "prefix" => "mb", "prefix_name" => "megabyte", "pow_value" => "2"),     array("id" => 3, "prefix" => "gb", "prefix_name" => "gigabyte", "pow_value" => "3"),     array("id" => 4, "prefix" => "tb", "prefix_name" => "terabyte", "pow_value" => "4"),     array("id" => 5, "prefix" => "pb", "prefix_name" => "petabyte", "pow_value" => "5"),     array("id" => 6, "prefix" => "eb", "prefix_name" => "exabyte", "pow_value" => "6"),     array("id" => 7, "prefix" => "zb", "prefix_name" => "zettabyte", "pow_value" => "7")     ); 

so entries like:

    asset_id, asset_memory, asset_memory_prefix     1, 1024, 2     2, 1024, 3     3, 2048, 2 

how in select?

do want group by?

select asset_id, sum(asset_memory) asset_memory, asset_memory_prefix assets group asset_id, asset_memory_prefix; 

edit:

i think want, assuming asset_memory_prefix table:

select a.asset_id,        sum(pow(1000, ap.pow_value) * asset_memory) total_memory_bytes assets join      asset_prefix ap      on a.asset_memory_prefix = ap.asset_memory_prefix group a.asset_id; 

i'm not sure if want 1,000 or 1,024 (2^10).

you should able use asset_memory_prefix directly:

select a.asset_id,        sum(pow(1024, ap.asset_memory_prefix) *            asset_memory           ) total_memory_bytes assets join      asset_prefix ap      on a.asset_memory_prefix = ap.asset_memory_prefix group a.asset_id;