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