To calculate sum() two alias named columns - in sq

问题:

To calculate sum() of two temp column names declared in query - in SQL

stud table has only two columns m1,m2. total and total1 is given as temp name.

select 
   m1, m2, 
   SUM(m1) + SUM(m2) as Total,
   SUM(m1) + SUM(m2) as Total1 
from 
   stud 
group by 
   m1, m2

How to calculate grandtotal as sum(total)+sum(total1) with the column name declared as temp name for the query to execute.

With cte dosn't support duplicate column names?

How to make use of it to support duplicate columnname

回答1:

You can't do it directly - you need to use something like a CTE (Common Table Expression) - like this:

;WITH sums AS 
(
   SELECT
      m1, m2, 
      SUM(m1) + SUM(m2) as Total,
      SUM(m1) + SUM(m2) as Total1 
   FROM
      dbo.stud 
   GROUP BY
      m1, m2
)
SELECT 
   m1, m2, 
   total, total1, 
   total+total1 AS 'GrandTotal' 
FROM 
   sums

This works in SQL Server 2005 and newer (and also in some other database systems that support CTE's - which is an ANSI standard).

回答2:

select convert(int, first)+ convert(int,second) as total from test1 

in this first and second is field datatype is nvarchar , and if fields are in integer then

select first+second as total from test1 , test1

is table name.