Split column in 3

Note: Tried a couple of the answers below (its in Teradata, so some of the answers are giving me syntax errors everywhere)

I hit a brick wall here. I want to compare year by year in different columns

ID, Year, Revenue 
1, 2009, 10 
1, 2009, 20 
1, 2010, 20 
2, 2009, 5 
2, 2010, 50
2, 2010, 1

How do I separate it by both ID and Year?

At the end I would like it to look like this

ID, Year, Sum
1, 2009, 30
1, 2009, 20
...
2, 2010, 51

(heavi开发者_Python百科ly edited for comprehension)

The best I can give you with the amount of detail you have provided is to break your table into subqueries:

select t1.yr - t2.yr from 
 (select yr 
   from the_table where yr = 2010) t1, 
 (select yr 
   from the_table where yr = 2010) t2

More detail could be given if we knew which type of database you are using, what the real structure of your table is, etc. but perhaps this will get you started.

something like this:

select id, t2009.year, t.2010.year, t2010.year-t.2009.year diff
from
( select id, year
from mytable
where year = 2009
) t2009
,
( select id, year
from mytable
where year = 2010
) t2010

You will most likely have to do a self-join

SELECT [what you are comparing] FROM [table] t1
  [INNER/LEFT] JOIN [table] t2 ON t1.[someID] = t2.[someID]
WHERE t1.year = 2009 AND t2.year = 2010

In the someID would not necessarily have to be an ID, or even an indexed column, but it should be the column you are looking to compare across the years.

E.g. a table called 'Products' with columns/fields

• ID
• ProductName
• Price
• Year

You could do:

SELECT t1.ProductName, (t2.Price - t1.Price) As Price_change FROM Products t1
  INNER JOIN Products t2 ON t1.ProductName = t2.ProductName
WHERE t1.year = 2009 AND t2.year = 2010

This would be faster is ProductName was a primary key or an indexed column. This would also be faster than using nested selects which are much much slower than joins (when joining on an index).

By your data and your desired output, I think you simply want this:

select ID, Year, SUM(Revenue)
from YourTable
GROUP BY ID, Year

Update

Now, if your first data sample is already a SELECT query, you need to:

select ID, Year, SUM(Revenue)
from (SELECT...) YourSelect
GROUP BY ID, Year

This looks like a good candidate for the ROLLUP command. It will give you automatic sums for the grouped-by columns:

GROUP BY ROLLUP (ID,Year)

More info here.