CODEWITHSUNDEEP
sqlsql-serverselectsum
Niar
Niar

Reputation: 540

How to get sum of rows in sql

I need to get total of each row, but i can't take the alias name for the row as it gives error. How to get sum() for the row in the below statement.

select sum(Amount) as [Amount total],
       sum(balance) as [Balance Total],
       sum(Remaining) as [Remaining Total]
from MyTable

I want to do something like

select sum(Amount) as [Amount total],
       sum(balance) as [Balance Total],
       sum(Remaining) as [Remaining Total],
       [Amount total]+[Balance Total]+[Remaining Total] as Total
from MyTable

Upvotes: 0

Views: 772

Answers (3)

Tab Alleman
Tab Alleman

Reputation: 31785

You don't have to SUM the SUMS, just SUM the columns that they are SUMming, and you will get the total sum:

select sum(Amount) as [Amount total],sum(balance)[Balance Total]
,sum(Remaining)[Remaining Total],SUM(Amount+Balance+Remaining) as Total
    from MyTable

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT SUM(Amount) AS [Amount total], 
       SUM(balance) AS [Balance Total], 
       SUM(Remaining) AS [Remaining Total], 
       SUM(Amount + balance + Remaining) AS Total
FROM MyTable

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Repeat the expression, use a subquery, or use a CTE. Here is the first choice:

select sum(Amount) as [Amount total],
       sum(balance) as [Balance Total],
       sum(Remaining) as [Remaining Total],
       (sum(Amount) + sum(balance) + sum(Remaining)) as Total
from MyTable;

Column aliases are not recognized at the same level where they are defined.

Upvotes: 2

Related Questions