SQL Server Performance

Sums

Discussion in 'SQL Server Reporting Services' started by jastone, Apr 30, 2007.

  1. jastone New Member

    i would like to sum up a field as a subtotal on a table, but i want it to only be if another field is equal to a certain value,

    an example would be if i had line items on an invoice that were EITHER taxed on the state or federal level, but the tax field came in as the tax, and there was another field denoting state or federal, then a subtotal of the state taxes and a subtotal in the same row of a footer

    so if i had three rows
    "Item" "Price" "Tax Type" "Tax Amount"

    Item1 Price1 State 10.00
    Item2 Price2 Federal 14.00
    Item3 Price3 State 6.00

    Footer
    column 1: "Total State: " Column2: "$16.00" Column3: "Total Federal: " Column4: "$14.00"

    thanks in advance

    Joe Janka
  2. ndinakar Member

    You can do the formatting at your front end but something like this could work:



    Declare @table table (Item varchar(10), Price varchar(10), [Tax Type] varchar(10), [Tax Amount] decimal(10,2))
    insert into @table select 'Item1', 'Price1', 'State', 10.00 union all
    select 'Item2' ,'Price2' , 'Federal', 14.00 union all
    select 'Item3' ,'Price3' , 'State' , 6.00

    select 'Total State:', TotalState = (Select sum(Case when [Tax Type] ='State' then [Tax Amount] Else 0 end) from @table)
    ,'Total Federal:', TotalFederal = (Select sum(Case when [Tax Type] ='Federal' then [Tax Amount] Else 0 end) from @table)



    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/

Share This Page