SQL Statement - want daily dates rolled up and displayed as Year

Question

I have two years worth of data that I'm summing up for instance

Date | Ingredient_cost_Amount| Cost_Share_amount |

I'm looking at two years worth of data for 2012 and 2013, I want to roll up all the totals so I have only two rows, one row for 2012 and one row for 2013. How do I write a SQL statement that will look at the dates but display only the 4 digit year vs 8 digit daily date. I suspect the sum piece of it will be taken care of by summing those columns withe calculations, so I'm really looking for help in how to tranpose a daily date to a 4 digit year. Help is greatly appreciated.

Answer 1

select DATEPART(year,[Date]) [Year]
, sum(Ingredient_cost_Amount) Total
from @table
group by DATEPART(year,[Date])

Answer 2

Define a range/grouping table.
Something similar to the following should work in most RDBMSs:

SELECT Grouping.id, SUM(Ingredient.ingredient_cost_amount) AS Ingredient_Cost_Amount,
                    SUM(Ingredient.cost_share_amount) AS Cost_Share_Amount  
FROM (VALUES (2013, DATE('2013-01-01'), DATE('2014-01-01')),
             (2012, DATE('2012-01-01'), DATE('2013-01-01'))) Grouping(id, gStart, gEnd)
JOIN Ingredient
  ON Ingredient.date >= Grouping.gStart
     AND Ingredient.date < Grouping.gEnd
GROUP BY Grouping.id

(DATE() and related conversion functions are heavily DB dependent. Some RDBMSs don't support using VALUES this way, although there are other ways to create the virtual grouping table)
See this blog post for why I used an exclusive upper bound for the range.

Using a range table this way will potentially allow the db to use indices to help with the aggregation. How much this helps depends on a bunch of other factors, like the specific RDBMS used.