locked
SUM and actual value RRS feed

  • Question

  • Hi all,I am trying to write a query for the following example.

    I have a table with sales figures for a product over a number of years

    I want to be able to write a query that gives the acual value for the product for a given year but also sums up the value for the product across all years.

    SELECT ProductName, ProductSales, SUM(ProductSales), Year

    FRM Products

    Is this possible?

    Thanks,

    Sam

    Monday, February 27, 2012 5:19 PM

Answers

  • Try:

    SELECT ProductName, SUM(ProductSales) AS [All Years Total], SUM(case when Year = Year(CURRENT_TIMESTAMP) then ProductSales ELSE 0 END) as [Current Year Total] FROM Products

    GROUP BY ProductName



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by Brian Tkatch Monday, February 27, 2012 6:34 PM
    • Marked as answer by Kalman Toth Sunday, March 4, 2012 2:12 PM
    Monday, February 27, 2012 5:22 PM

All replies

  • Try:

    SELECT ProductName, SUM(ProductSales) AS [All Years Total], SUM(case when Year = Year(CURRENT_TIMESTAMP) then ProductSales ELSE 0 END) as [Current Year Total] FROM Products

    GROUP BY ProductName



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by Brian Tkatch Monday, February 27, 2012 6:34 PM
    • Marked as answer by Kalman Toth Sunday, March 4, 2012 2:12 PM
    Monday, February 27, 2012 5:22 PM
  • Declare @Year SmallInt 
    Set @Year = 2012
    Select ProductName, ProductSales, Sum(ProductSales) As CurrentYearSale, Year, AllYearInfo.AllYearsSales 
    From Products
    Outer Apply (Select Sum(SubQry.ProductSales) As AllYearsSales From Products As SubQry Where SubQry.ProductID = Products.ProductID) As AllYearInfo
    Where Year = @Year


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Monday, February 27, 2012 5:23 PM
  • SELECT ProductName, ProductSales, SUM(ProductSales) OVER (PARTITION BY ProductName), Year
    FROM Products

    Tom
    Monday, February 27, 2012 5:23 PM