to calculate sum() two alias named columns - in sql

Question

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

Answer 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).