CODEWITHSUNDEEP
sql
Lee.B
Lee.B

Reputation: 51

How to sum multiple columns in SQL

I have database that has 12 columns of numeric data - one column for each month.

Column names are 

Jan, Feb, Mar. Apr, May, Jun, Jul, Aug, Sep, Oct, Nov and Dec.

I know how to write code to sum first 3 months (Jan, Feb, and Mar) and return value in Total. 

SELECT Jan+Feb+Mar AS Total FROM MyData

Question: How do I write generic query to give me sum of first N months?

Do I need to write code to generate the SQL statement or is there another way?

Upvotes: 5

Views: 36698

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

If, you are working with SQL Server, then you could use APPLY operator to generate the month sequence.

SELECT
       SUM(a.Total) Totals 
FROM table t
CROSS APPLY (
        VALUES (1, 'Jan', Jan), (2, 'Feb', Feb), (3, 'Mar', Mar),
               ....
               (12, 'Dec', Dec)
)a(Seq, Months, Total)
WHERE a.Seq <= 3 -- pass Nos of Months to get the sum of Totals

Upvotes: 0

Paritosh
Paritosh

Reputation: 11570

You can generate a string which you can execute using sp_executesql command

DECLARE @query NVARCHAR(MAX);
SET @query = 'SELECT #selectColumns# FROM tableName'

--- write your logic to get how many columns you need based on value of `N`
--- make their summation  as stringToReplace (i.e. = 'Jan + Feb + Mar ...')
--- and replace them with #selectColumns# in @query

EXECUTE(@query)

PS: if you are using database other than MSSQL Server, you should be able to find some way to execute string as SQL command.

For example, for MySQL, there is prepare

PREPARE stmt1 FROM @query; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1;

For Oracle, you'll be having EXEC SQL EXECUTE IMMEDIATE :query;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

I think you would need dynamic SQL to auto generate a query with the first N months. But you could also define a view which contains all the sums, e.g.

CREATE VIEW sums AS (
    SELECT Jan AS total1,
    SELECT Jan + Feb AS total2,
    SELECT Jan + Feb + Mar AS total3,
    ...
    SELECT Jan + Feb + Mar + ... + Nov + Dec AS total12
    FROM yourTable
)

Then, you could query the view and access any sum you want.

Upvotes: 3

Related Questions