How to Find Sum of Multiple Columns in a Table in SQL Server 2005

Find the sum of columns depending on the current month in SQL Server 2005

If the change suggested in my other answer is too radical to adopt, then here's an alternative to @Filip De Vos's solution:

SELECT
  SUM(
    CASE v.number
      WHEN  1 THEN Jan
      WHEN  2 THEN Feb
      WHEN  3 THEN Mar
      WHEN  4 THEN Apr
      WHEN  5 THEN May
      WHEN  6 THEN Jun
      WHEN  7 THEN Jul
      WHEN  8 THEN Aug
      WHEN  9 THEN Sep
      WHEN 10 THEN Oct
      WHEN 11 THEN Nov
      WHEN 12 THEN Dec
    END
  ) AS Total
FROM MonthlyData m
  CROSS JOIN master..spt_values v
WHERE v.type = 'P'
  AND v.number BETWEEN 1 AND MONTH(GETDATE())

The master..spt_values table is a system table used for internal purposes, but can also be used in user queries. One of its subsets contains a list of integers from 0 to 2047, which can be used in many cases as a ready-made tally table.

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

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

CTE not able to sum blank columns out of multiple columns

It looks like the PR column might be returning NULL, which would cause the calculation to not be performed. As I mentioned in the comments, maybe a CASE statement within the SELECT would fix the issue.

SELECT ormonth, CC_O, CC_R, PO, PR, PO_O, PO_R, 
       (CC_O + PO + PO_O) AS Total_Orders, 
       (CC_R + CASE WHEN PR IS NULL THEN 0 ELSE PR END + PO_R) AS Total_Revenue
FROM   cte_Revenue

Another option would be to ensure that within the CTE, the PR column would always have a value. Putting in ELSE statements in the CTE that would return 0, might help as well.

For example:

 COUNT(CASE WHEN Orders.paymentmethodid = 1 THEN Orders.paymentmethodid 
            ELSE 0 END) as 'PO_O',
 SUM(CASE WHEN Orders.paymentmethodid = 1 THEN Orders.PaymentAmount  
            ELSE 0 END ) as 'PO_R'

How to sum dynamic columns in SQL Server?

If your table is small (i.e. 10 columns) I would just do it manually. But if it's like 20+ columns, I would employ some dynamic sql.

To answer your question directly, yes, you can dynamically create a table with dynamic column names using dynamic sql.

Here's one way to do it:
You can use INFORMATION_SCHEMA.COLUMNS View to get all the column names and put them in a temp table.

SELECT NAME INTO #COLUMNS 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = YourTable

Next, create a temp table to store your sums.

CREATE TABLE #SUMS (
COLUMN_NAME NVARCHAR(MAX),
SUM_COLUMN INT
)

You can then use dynamic sql and a loop to sum each column.

WHILE EXISTS(SELECT TOP 1 * FROM #COLUMNS)
    BEGIN
        DECLARE @COLUMN NVARCHAR(MAX) = (SELECT TOP 1 * FROM #COLUMNS)
        DECLARE @DYNAMICSQL NVARCHAR(MAX) = N'SELECT ' + @COLUMN + ' AS COLUMN_NAME, SUM(' + @COLUMN + ') FROM YourTable'

        INSERT INTO #SUMS
        EXEC SP_EXECUTESQL @DYNAMICSQL

        DELETE FROM #COLUMNS
        WHERE NAME = @COLUMN
    END

Then you would need another dynamic sql and loop to loop through the new table and create a temp table with the column names you want using the sum values and the table name you want.

You should be able to do that using the code already supplied above.

sql server 2008 r2 - multiple columns each with sum on dependent variable

You may use conditional aggregation here:

SELECT
    code1,
    code2,
    SUM(CASE WHEN identifier = 1 THEN dollars ELSE 0 END) AS identifier1_sum,
    SUM(CASE WHEN identifier = 2 THEN dollars ELSE 0 END) AS identifier2_sum
FROM yourTable
GROUP BY
    code1,
    code2;

Demo

Calculate total of total column in SQL Server 2005

You could use the WITH ROLLUP option in the GROUP BY - this will give you an extra row that contains the "rolled up" (summed up) values:

SELECT  Month
    , SUM(Man) AS Man
    , SUM(Sal) AS Sal
    , SUM(Man + Sal) AS Total
FROM    (        
      SELECT  DATENAME(MONTH, DOB) AS Month
              , CASE WHEN TypeOfPost = 'Manager' THEN 1 ELSE 0 END AS Man
              , CASE WHEN TypeOfPost = 'Sales' THEN 1 ELSE 0 END AS Sal
      FROM    tableName
    ) g          
GROUP BY
    Month WITH ROLLUP

The extra line will contain NULL for the Month column, and should sum up all other columns for you

Sum of particular columns by day of week

Use extract(dow ...) in the where clause to filter all weekday (or weekend) rows and count them:

select count(*) as weekdays
from rentalinfo
where extract(dow from rental_date) in (1, 2, 3, 4, 5)

Or use conditional aggregation:

select count(case when extract(dow from rental_date) in (1, 2, 3, 4, 5) then 1 end) as weekdays
     , count(case when extract(dow from rental_date) in (0, 6) then 1 end) as weekends
from rentalinfo

Multiply 2 columns in sql and to sum all the results using SQL

Original Answer

select Sum(Rate) as Rate, Sum(Qty) as Qty, Sum(Rate*Qty) as Result from tblName

EDIT - Try this..

select 
     0 as TotalRow, 
     Rate,
     Qty,
    (Rate*Qty) as Result 
from tblName

UNION ALL

select 
     1 as TotalRow,
     Sum(Rate) as Rate, 
     Sum(Qty) as Qty, 
     Sum(Rate*Qty) as Result 
 from tblName

Order By TotalRow

---

Related Topics