May 9, 2019 at 2:24 pm
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
May 9, 2019 at 2:33 pm
Is this what you're after? If not, can you post your expected results please (don't forget to use the Insert/Edit Code sample button)
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;
--Your old query
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];
*/
--Guessed solution
SELECT [Type],
SUM(item_fee) AS item_fee,
SUM(dbf_fee) AS dbf_Fee,
SUM(pcc_fee) AS pcc_fee,
SUM(CASE [Type] WHEN 'a' THEN NULL ELSE item_fee + dbf_fee + pcc_fee END) AS total_fee
FROM #temp
GROUP BY [Type];
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 9, 2019 at 3:00 pm
Thank you for the reply Thorn. The example below are the results I would like to see.
typeitem_feedbf_Fee pcc_feebooking_total_fee
a4.00 0.00 0.004.00
b0.00 8.00 0.008.00
c0.00 0.00 12.0012.00
So sum the 'a' rows but just the item_fee column, ignore the values in the dbf_fee and the pcc_fee, treat them as a zero value.
Does that make sense?
Thanks for your help.
May 9, 2019 at 3:11 pm
This appears to be what your after then. I perform the conditional aggregation inside a CTE, as then you don't need to repeat the CASE expressions for the total:
WITH CTE AS(
SELECT [type],
SUM(CASE [Type] WHEN 'a' THEN item_fee ELSE 0 END) AS item_fee,
SUM(CASE [Type] WHEN 'b' THEN dbf_fee ELSE 0 END) AS dbf_fee,
SUM(CASE [Type] WHEN 'c' THEN pcc_fee ELSE 0 END) AS pcc_fee
FROM #temp
GROUP BY [type])
SELECT [Type],
item_fee,
dbf_fee,
pcc_fee,
item_fee + dbf_fee + pcc_fee AS booking_total_fee
FROM CTE;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply