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

If you post code or XML, please highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it! — marc_s
– marc_s, Commented Oct 14, 2010 at 10:32

marc_s · Accepted Answer · 2010-10-14 10:39:24Z

28

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

answered Oct 14, 2010 at 10:39

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BenMorel · Accepted Answer · 2014-06-16 11:47:01Z

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

edited Jun 16, 2014 at 11:47

BenMorel

37k52 gold badges208 silver badges339 bronze badges

answered Feb 5, 2013 at 9:18

shweta

1

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related