sum of sums of columns

Hi all,

I'va many columns in wich I've some amounts. I need to get the total for
every client with also the total of totals...

my table.

idclient mediumint unsigned not null,
amount1 float(10,2) unsigned,
amount2 float(10,2) unsigned,
amount3 float(10,2) unsigned;

now I do this: select sum(amount1) as total1, sum(amount2) as total2,
sum(amount3) as total3 from amounts group by idclient.

and I need also this: sum(amount1+amount2+amount3) as total

How can I do in MySQL ?

Thanks for help.

Bob

> I'va many columns in wich I've some amounts. I need to get the total for
> every client with also the total of totals...
>
> my table.
>
> idclient mediumint unsigned not null,
> amount1 float(10,2) unsigned,
> amount2 float(10,2) unsigned,
> amount3 float(10,2) unsigned;
>
> now I do this: select sum(amount1) as total1, sum(amount2) as total2,
> sum(amount3) as total3 from amounts group by idclient.
>
> and I need also this: sum(amount1+amount2+amount3) as total
This does actually work but if you have one value = null then the result is
null...that's why it didn't work since the beginning for me.

Cheers

"Bob Bedford" <bob@bedford.com> schreef in bericht
news:45f59403$0$3817$5402220f@news.sunrise.ch...
>> I'va many columns in wich I've some amounts. I need to get the total for
>> every client with also the total of totals...
>>
>> my table.
>>
>> idclient mediumint unsigned not null,
>> amount1 float(10,2) unsigned,
>> amount2 float(10,2) unsigned,
>> amount3 float(10,2) unsigned;
>>
>> now I do this: select sum(amount1) as total1, sum(amount2) as total2,
>> sum(amount3) as total3 from amounts group by idclient.
>>
>> and I need also this: sum(amount1+amount2+amount3) as total
> This does actually work but if you have one value = null then the result
> is null...that's why it didn't work since the beginning for me.
>
> Cheers
>
>

select sum(amount1) as total1, sum(amount2) as total2, sum(amount3) as
total3 , sum(IFNULL(amount1,0)+IFFNULL(amount2,0)+IFNULL(am ount3,0)) as
total4 from amounts group by idclient.


see: http://dev.mysql.com/doc/refman/4.1/...functions.html