CODEWITHSUNDEEP
sqlsql-serversumfield
user5963361
user5963361

Reputation: 13

Sum two columns from temp table

I'm new to SQL and not sure how to sum two columns from a temp table, any help welcome.

Script is as below:

SELECT
    Provider,
    StartYear,
    CAST(YEAR([EndYear]) AS INT) AS 'EndYear'
INTO 
    #a
FROM 
    TABLE1

SELECT
    *, SUM(EndYear - StartYear) AS 'Years'
FROM 
    #a

I get the error message:

a.provider is invalid in the select list because it is not contained in either an aggregate function or GROUP BY clause

Sample Data

Provider    StartYear   EndYear 
A1          1940        15/04/2016 00:00 
A2          1930        08/06/2016 00:00 
A3          1932        04/06/2016 00:00 
A2          1932        15/06/2016 00:00 
A1          1944        01/07/2016 00:00

Upvotes: 1

Views: 1332

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

You don't need a temporary table. I think you want something like this:

SELECT t1.*,
       (t1.EndYear - t1.StartYear) as Years
FROM table1 t1;

or:

SELECT t1.*,
       DATEDIFF(year, t1.StartYear, t1.EndYear) as Years
FROM table1 t1;

Edit: If you actually want to sum the values, just remove the t1.* and add sum():

SELECT SUM(t1.EndYear - t1.StartYear) as Years
FROM table1 t1;

In your case, you should fix the names of your columns. Something called "Year" should not be a datetime. But, it is easy enough to convert:

SELECT SUM(YEAR(t1.EndYear) - t1.StartYear) as Years
FROM table1 t1;

Upvotes: 1

Related Questions