February 7, 2017 at 5:14 am
Hi there,
I have a financial source table which I connect to via Toad (Oracle) containing line type codes with financial amounts and I need to know the most efficient way of writing SQL which extracts this data and then groups it and sums it dependent on the line type code.
My source data looks like this:
Contract Number Line Type Amount
1478 A1 10.00
1478 A2 5.00
1478 A3 3.00
1478 B1 20.00
1478 B2 25.00
1478 B3 33.00
Line Types A1, A2 and A3 relate to Orders and line types B1, B2 and B3 relates to Sales
In my output I'd like to see
Contract Number Sum_Orders Sum_Sales
1478 18.00 78.00
Thanks
February 7, 2017 at 5:24 am
Something like this?CREATE TABLE #Sample (Contract INT,
Line VARCHAR(5),
Amount DECIMAL(12,2));
INSERT INTO #Sample
VALUES (1478, 'A1', 10.00),
(1478, 'A2', 5.00),
(1478, 'A3', 3.00),
(1478, 'B1', 20.00),
(1478, 'B2', 25.00),
(1478, 'B3', 33.00);
GO
SELECT *
FROM #Sample;
SELECT S.Contract,
SUM(CASE WHEN LEFT(S.Line, 1) = 'A' THEN Amount ELSE 0 END) as Orders,
SUM(CASE WHEN LEFT(S.Line, 1) = 'B' THEN Amount ELSE 0 END) as Sales
FROM #Sample S
GROUP BY S.Contract;
GO
DROP TABLE #Sample;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 7, 2017 at 5:25 am
Do a SUM grouped by ContractNumber and LEFT(LineType, 1), then PIVOT to get it all in one row (or, better still, have your presentation layer do the pivoting for you).
Edit: Thom's solution is a little simpler and more elegant than mine (not to mention that he's also gone to the trouble to create your sample data and write the query for you), so I recommend you use that.
John
February 7, 2017 at 5:29 am
chris.evans 94907 - Tuesday, February 7, 2017 5:14 AMHi there,I have a financial source table which I connect to via Toad (Oracle) containing line type codes with financial amounts and I need to know the most efficient way of writing SQL which extracts this data and then groups it and sums it dependent on the line type code.
My source data looks like this:
Contract Number Line Type Amount
1478 A1 10.00
1478 A2 5.00
1478 A3 3.00
1478 B1 20.00
1478 B2 25.00
1478 B3 33.00Line Types A1, A2 and A3 relate to Orders and line types B1, B2 and B3 relates to Sales
In my output I'd like to see
Contract Number Sum_Orders Sum_Sales
1478 18.00 78.00Thanks
-- this is just a CTE to run the query against
;WITH Transactions ([Contract Number], [Line Type], Amount) AS (
SELECT 1478, 'A1', 10.00 UNION ALL
SELECT 1478, 'A2', 5.00 UNION ALL
SELECT 1478, 'A3', 3.00 UNION ALL
SELECT 1478, 'B1', 20.00 UNION ALL
SELECT 1478, 'B2', 25.00 UNION ALL
SELECT 1478, 'B3', 33.00
)
-- this is the query, you may have to change the way columns are named (using AS instead of =)
SELECT
[Contract Number],
Sum_Orders = SUM(CASE WHEN [Line Type] IN ('A1','A2','A3') THEN Amount ELSE NULL END),
Sum_Sales = SUM(CASE WHEN [Line Type] IN ('B1','B2','B3') THEN Amount ELSE NULL END)
FROM Transactions
GROUP BY [Contract Number]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply