How do I compute for the total of the value in Alias vladd1, vladd2, vladd3, vladd...

Question

question

cyberarnet2 asked Nov 23, '09

How do I compute for the total of the value in Alias vladd1, vladd2, vladd3, vladd...

Hi,

How do I compute for the total of the value in Alias vladd1, vladd2, vladd3, vladd4 or any alternative using declare statement ? Here is my sample statement below.

                    
select alan8,                      
case                       
when rtrim(aladd1)  '' then len(aladd1) + 9 else 0 end as vladd1 ,                      
case                      
  when rtrim(aladd1)  '' then len(aladd2) + 9 else 0 end as vladd2 ,                      
case                      
  when rtrim(aladd1)  '' then len(aladd3) + 9 else 0 end as vladd3 ,                      
case                      
  when rtrim(aladd1)  '' then len(aladd4) + 9 else 0 end as vladd4,                      
vladd1 + vladd2 + vladd3 + vladd4 as Total                      
 from Customers

Thanks and appreciate your help.

Regards, cyberarent2

sql-server-2000 aggregates

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-11-23T04:51:52Z

Peso answered Nov 23, '09

Something similar to this?

select  	alan8,            
    	sum(case when aladd1 > '' then len(aladd1) + 9 else 0 end) as vladd1,            
    	sum(case when aladd2 > '' then len(aladd2) + 9 else 0 end) as vladd2,            
    	sum(case when aladd3 > '' then len(aladd3) + 9 else 0 end) as vladd3,            
    	sum(case when aladd4 > '' then len(aladd4) + 9 else 0 end) as vladd4,            
    	SUM(len(aladd1) + len(aladd2) + len(aladd3) + len(aladd4) + 36) as Total            
from    	Customers             
group by    alan8

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question