June 20, 2012 at 2:18 pm
Pretty quick question since I'm fairly sure the answer is no ... with data such as this:
CREATE TABLE #Test
(
ID INT IDENTITY PRIMARY KEY,
Cat INT,
Col1 INT,
Col2 INT,
Col3 INT
)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (1, 10, 0, 5)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (1, 11, 0, 5)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (1, 12, 0, 2)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (1, 13, 0, 4)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (2, 14, 1, 2)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (2, 15, 2, 3)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (2, 16, 3, 4)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (2, 17, 4, 1)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (3, 22, 1, 2)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (3, 23, 2, 4)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (3, 24, 0, 30)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (3, 25, 5, 15)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (4, 33, 12, 23)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (4, 32, 0, 42)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (4, 31, 0, 12)
INSERT INTO #Test (Cat, Col1, Col2, Col3)
VALUES (4, 30, 0, 41)
Is there any way to get a query such as this, to work using one SELECT instead of using a cte or some other form of subquery to get subtotals first?
WITH cte AS
(
SELECT
Cat,
(CASE WHEN SUM(Col2) > 0 THEN SUM(Col1) / SUM(Col2) ELSE 0 END) * SUM(Col3) AS TopSum,
SUM(Col3) AS BotSum
FROM #Test
GROUP BY Cat
)
SELECT
(CASE WHEN SUM(BotSum) > 0 THEN SUM(TopSum) / SUM(BotSum) ELSE 0 END)
FROM cte
June 20, 2012 at 2:50 pm
You could probably use the windowing functions with the SUM to get the desired output, but I've found that the windowing functions are not terribly efficient, so you're probably better off using the CTE anyhow.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply