SQL Server Performance Forum – Threads Archive

Sums

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

---

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/

---

]]>