SQL Challenge!
-
Hi,
I'm trying to figure a set-based way to do the following...
I have a table:
TableA
ColA ColB 1 100 2 200 3 300 3 200 I'd like to perform logic on this table to get me:
TableB
ColA ColB 1 800 2 700 3 500 800 = 100+200+300+200
700 = 200+300+200
500 = 300+200
Cheers
-
Hi Sho,
This works against your sample data, but I've a feeling you're going to post more data where this doesn't give what you're looking for!
Anyway, take a look and see what you think...
--data
declare @TableA table (ColA int, ColB int)
insert @TableA
select 1, 100
union all select 2, 200
union all select 3, 300
union all select 3, 200
--calculation
select a.ColA, sum(b.ColB) as ColB
from @TableA a cross join @TableA b
where a.ColA < b.ColA or (a.ColA = b.ColA and a.ColB = b.ColB)
group by a.ColA
/* results
ColA ColB
----------- -----------
1 800
2 700
3 500
*/
Cheers,
Ryan
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Here's another way to skin the cat ....
Your table is "dbo.challenge"
SELECT
colA
,SUM(colB)
FROM
(SELECT
a.colA
,colB = CASE WHEN b.colA < a.colA THEN SUM(b.colB) ELSE b.colB END
FROM
dbo.challenge a
LEFT JOIN dbo.challenge b
ON a.colA <= b.colA
GROUP BY
a.colA
,b.colA
,b.colB) c
GROUP BY
colA
And after looking at the execution plans, it appears the only difference is the cache size.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Ryan, Jason... thanks for the replies!! Genius!
Ryan, I've tried yours and it works a treat (so far:-)
Jason, I'll give yours a bash tomorrow... it's late and it's hometime.
Thanks again!
-
Hmmm - I'm surprised!
I reckon you should use Jason's since what I gave won't give you what I think you want for some sets of data (that was a mouthful!). I can't tell for sure from your sample data though.BTW, if you're using SQL 2005, here's an alternative...
with c as (select ColA, sum(ColB) as ColB from TableA group by ColA)
select a.ColA, sum(b.ColB) as ColB from c a left outer join c b on a.ColA <= b.ColA group by a.ColA
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Hi Ryan. Long time no see.
LEFT JOIN?
DECLARE
@Sample TABLE (ColA INT, ColB INT)
INSERT
@Sample
SELECT 1, 100 UNION ALL
SELECT 2, 200 UNION ALL
SELECT 3, 300 UNION ALL
SELECT 3, 200
;WITH Yak (ColA, ColB)
AS (
SELECT ColA,
SUM(ColB)
FROM @Sample
GROUP BY ColA
)
SELECT y1.ColA,
SUM(y2.ColB)
FROM Yak AS y1
INNER JOIN Yak AS y2 ON y2.ColA >= y1.ColA
GROUP BY y1.ColA
ORDER BY y1.ColA
N 56°04'39.16"
E 12°55'05.25" -
Hi Peso - wrong site for the yak reference isn't it?
I just realised, we can use this little trick...
SELECT
ColA, (SELECT SUM(ColB) FROM @TableA WHERE ColA >= a.ColA) AS ColB FROM @TableA a GROUP BY ColA
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Heh... a Yak by any other name...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply