sql calculation

[darrengan]

Hie all,
I just started using php and mysql for the past 1 month and slowly picking things up :)

i got a question on DB calculation. Pls refer the table below:

account number ¦ payment
-----------------------------
45621 ¦ $100.00
45621 ¦ $50.00
42000 ¦ $70.00
42000 ¦ $20.00
42000 ¦ $30.00
45656 ¦ $95.00

For the account number, notice that there is duplicate account number with different payment value. Now, how do I calculate the total value for each account ie: for 45621 = $ 150.00 and 42000 = $120.00?

There is a SQL statement (distinct) which will only eliminate duplicate row.

Thanks in advance for the reply ...
Cheeers...

[incywincy]

hi darrengan,

welcome to WebmasterWorld

you could try

select distinct account_number,sum(payment) from table_name group by account_number order by 2;

if you want highest payments at the top of the report use

select distinct account_number,sum(payment) from table_name group by account_number order by 2 desc;

hope this helps

[darrengan]

Hie incywincy,
Thanks for the fast reply.. i just posted this msg 10 minutes ago and now i have a reply :)

I will try on it later as i am not with the development pc now. Sure it helps.

Will feedback later and CHEERS!

[darrengan]

Hie incywincy,

it works as what 1 want.. TQ very much.

I have another question and it is regarding to check box:

<input type="checkbox" name="barring[]" value="IDD" checked /> IDD
<input type="checkbox" name="barring[]" value="STD" checked /> STD
<input type="checkbox" name="barring[]" value="MOB" checked /> MOB

okie.. i am using array to store the ticked value in MySql by using "serialize" and what is stored in the database looks like this
"a:3:{i:0;s:3:"IDD";i:1;s:3:"STD";i:2;s:3:"MOB";}"
If i were to unserialize it, it will only display as IDD, STD, MOD.

The question is how do i retrive the data in check box format again?

Can anyone please help?

[trajan]

$string = 'a:3:{i:0;s:3:"IDD";i:1;s:3:"STD";i:2;s:3:"MOB";}';
$arr = unserialize($string);
// walking through the array; a for-loop would also do it...
foreach($arr as $key => $val) {
echo '<input type="checkbox" name="barring[]" value="'.$val.'" checked /> '.$val.''; 
}

[brotherhood of LAN]

Also try

SELECT sum(account_number) FROM table WHERE account_number = $account_number;

[brotherhood of LAN]

^^^ should read

SELECT sum(payment) FROM table WHERE account_number = $account_number;