Sum field values with a twist!

Transact-SQL
1

Hi,

I have got a unique requirement here but I’m struggling to get the right results. I have table #temp, the data in this table can’t be updated permanently so the underlying table values can’t be changed permanently.

Below is all the code to create a sample table, the data and queries for my scenario.
Let me explain.

The type field will be 1 of 4 types ‘a’, ‘b’, ‘c’ and ‘d’. There are also three numeric columns. You will notice that type ‘b’, ‘c’ and ‘d’ only have 1 numeric value assigned per row in either item_fee, dfb_fee or pcc_fee. Type ‘a’ rows have values in all three numeric fields.

The aim is to group all the different ‘type’ records together and sum all the numeric field values into one temp colum. So the column ‘total_fee’ in the query below is the end result I am looking for, however, there is an issue, hence the post.

For all type ‘a’ rows I DO NOT want to include the values for the dbf_fee’s or pcc_fee’s, I just want to include the item_fee to the total_fee column.

I think I need a CASE query but I’ve been playing and am not sure if that is the way to go. Any advice would be appreciated. If you have any questions please let me know, thanks for looking.

SELECT  type, SUM(Item_Fee) AS item_fee, SUM(DBF_Fee) AS dbf_Fee, SUM(PCC_Fee) AS pcc_fee, 
SUM(Item_Fee) + SUM(DBF_Fee) + SUM(PCC_Fee) AS total_fee
FROM         #temp
GROUP BY type



CREATE TABLE #temp
(
    [Type] VARCHAR(50),
    item_fee numeric(18,2),
    dbf_fee numeric(18,2),
    pcc_fee numeric(18,2)
)

INSERT  INTO #temp VALUES
('a', 1, 2, 3),
('b', 0, 2, 0),
('c', 0, 0, 3),
('a', 1, 2, 3),
('b', 0, 2, 0),
('c', 0, 0, 3),
('a', 1, 2, 3),
('b', 0, 2, 0),
('c', 0, 0, 3),
('a', 1, 2, 3),
('b', 0, 2, 0),
('c', 0, 0, 3)

SELECT  *  FROM    #temp
2

Hope I understood your issue, try this and see if it works?
Pasi.

With CTE as (
SELECT
type,
Item_Fee,
DBF_Fee ,
PCC_Fee
FROM #temp
)
Select
SUM(Item_Fee) AS item_fee,
SUM(DBF_Fee) AS dbf_Fee,
SUM(PCC_Fee) AS pcc_fee,
SUM(Item_Fee + DBF_Fee + PCC_Fee) total_fee

from CTE
GROUP BY type, Item_Fee, DBF_Fee , PCC_Fee

3

I think this is what you are looking for:

   With totals
     As (
 Select [Type]
      , ItemTotal = sum(Item_Fee)
      , DBFTotal = sum(Case When [Type] = 'A' Then 0 Else DBF_Fee End)
      , PCCTotal = sum(Case When [Type] = 'A' Then 0 Else PCC_Fee End)
   From #temp
  Group By
        [Type]
        )
 Select [Type]
      , item_fee = ItemTotal
      , dbf_fee = DBFTotal
      , pcc_fee = PCCTotal
      , total_fee = ItemTotal + DBFTotal + PCC_Fee
   From totals;
2 Likes
4

Thank you Jeff, this helped a lot and me to the solution. Appreciate your time. Thanks again.

5

Thank you for your help Pasi.

1 Like