Query with aggregate Function

Question

Query with aggregate Function

Viky

SSC-Addicted

Points: 465
More actions
August 10, 2009 at 8:04 am

#137919

I need to show an aggegate value.
For Example I have a table with five rows:
id1 id2 value
1 1 240
2 1 120
3 2 870
4 2 60
5 3 450
The function Sum(value) returns a total group by id2, But I need a total per row group by id2, I need to return the following:
id1 id2 value total_acumulado
1 1 240 240
2 1 120 360 (240+120)
3 2 870 870
4 2 60 930
5 3 450 450
How can I do that?
Thanks a lot
Viky

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

This is doing 2 table scans so I know it isn't the most efficient code. You could probably put this into a CTE and get better performance but I don't have time to test another solution. If your result set is large let me know and I wil work on a better solution. Anyway, here is my hack at it.

CREATE TABLE #temp (id1 INT, id2 INT, value INT)

INSERT INTO #temp VALUES (1,1,240)

INSERT INTO #temp VALUES (2,1,120)

INSERT INTO #temp VALUES (3,2,870)

INSERT INTO #temp VALUES (4,2,60)

INSERT INTO #temp VALUES (5,3,450)

INSERT INTO #temp VALUES (6,2,70)

SELECT id1, id2, value,

(SELECT ISNULL(SUM(value),0) FROM #temp b WHERE b.id2 = a.id2 AND b.id1<a.id1)+value

FROM #temp a

DROP TABLE #temp

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

Matt Wilhoite (8/10/2009)
This is doing 2 table scans so I know it isn't the most efficient code. You could probably put this into a CTE and get better performance but I don't have time to test another solution. If your result set is large let me know and I wil work on a better solution. Anyway, here is my hack at it.

Oh... be careful. That constitutes what is known as a "Triangular Join". Read the article at the following URL for why they are so very, very bad. Their performance, depending on the number of rows in any given aggregate group, can be millions of times worse than a Cursor.

http://www.sqlservercentral.com/articles/T-SQL/61539/

I really need to finish the rewrite of my running total article because this is about the 5th or 6th similar request I've seen in the last 2 days. Give me a couple of minutes to put together the high speed alternative to this...

--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)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Viky,

You're really new to this forum so I have a suggestion... Take a look at the following article for how to post a problem... it'll help you get fully tested answers and it'll help you get them a lot quicker. Lot's of folks will simply skip over your request for help when you post data like you did. It's not your fault this time... you just didn't know.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

In the meantime, because you're new and haven't read the article, I'm working on your problem as we "speak". 🙂

--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)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Ok... I didn't make the assumption that ID1 actually contained the correct order in the table that you wanted to do the running total in. And, for a complete solution, I also assumed that you had an "original" table somewhere that you couldn't add a running total column to nor create the proper clustered index. The rest of my comments are in the code below...

---------------------------------------------------------------------------------

-- This first section is just a test setup and isn't a part of the solution.

---------------------------------------------------------------------------------

--===== Create a small test table and populate it.

-- This table represents your original table,

-- whatever it is. Don't worry... this is very fast and the ISNULL

-- makes a NOT NULL column so we can use it as a PK.

SELECT TOP 20000

ID1 = ISNULL(ROW_NUMBER() OVER (ORDER BY GETDATE()), 0),

ID2 = ABS(CHECKSUM(NEWID()))%5+1, --Random Integers from 1 to 5,

[Value] = ABS(CHECKSUM(NEWID()))%1000 --Random Integers from 0 to 999

INTO #OriginalTable

FROM Master.sys.All_Columns ac1 --Guaranteed 4000 rows min

CROSS JOIN Master.sys.All_Columns ac2 --Guaranteed 4000 rows min

--===== Let's add the what most people would use as a primary key

ALTER TABLE #OriginalTable

ADD PRIMARY KEY CLUSTERED (ID1)

---------------------------------------------------------------------------------

-- The solution begins here...

---------------------------------------------------------------------------------

--===== The technique we're getting ready to use requires a clustered index on

-- the required order that we want the running total aggregate to work on.

-- Since the original table already has a clustered index on the Primary

-- key, we need to do this all in a different table than the original

-- table. Don't worry... this is nasty fast... Notice that we also add

-- another column to the mix...

SELECT ID1,

ID2,

Value,

Total_Acumulado = CAST(NULL AS BIGINT) --Holds the running total

INTO #WorkTable

FROM #OriginalTable

ORDER BY ID2

--===== Now, we add the required clustered index that mimics the order we want

-- the running total to be done in. Again, this is very fast. Note that

-- we want to keep all the same ID2's together for this drill.

CREATE CLUSTERED INDEX IXC_WorkTable_RunningTotal

ON #WorkTable (ID2, ID1)

--===== Here's where the fun starts. Create some obviously named variables

DECLARE

@PrevID2 INT, --This will allow us to figure out when ID2 changes and acts as an anchor

@PrevTotal_Acumulado BIGINT --This will hold the running total as we go

--===== And here's the magic known as a "quirky update". It works just like you

-- would do it in a procedural program like VB or C. Read a row, update some

-- variables, write the running total variable back out to the row, read the

-- next row, and "loop" until we run out of rows. SQL Server DOES loop on

-- updates behind the scenes in what some of us call a "pseudo-cusor".

UPDATE wt

SET @PrevTotal_Acumulado = Total_Acumulado = CASE

WHEN @PrevID2 = ID2

THEN @PrevTotal_Acumulado + Value

ELSE Value

END,

@PrevID2 = ID2

FROM #WorkTable wt WITH (TABLOCKX)

OPTION (MAXDOP 1)

--===== Ok, let's see the results

SELECT *

FROM #WorkTable

ORDER BY ID2, ID1

--===== Clean up the mess from the test.

-- Obviously, don't do this in production!

DROP TABLE #OriginalTable, #WorkTable

Including the test setup, the whole think takes less than a second to create 20,000 rows, copy them to a working table, do the running total, AND display the results. All I can say is run that using triangular join code to see the real difference.

Post back if you have any questions.

--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)

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 5

Deleted Multiple Post

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 6

Deleted Multiple Post

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 7

Apologies for multiple posts....there was some problem with my internet connection. I have deleted the multiple posts anyway.

Viki - It would be instructive to familiarize yourself with Question Posting Best Practices. They do help get a speedier and more efficient response 🙂

declare @t table (id1 int, id2 int, value int)

insert into @t

select 1, 1, 240 union all

select 2, 1, 120 union all

select 3, 1, 120 union all

select 4, 2, 870 union all

select 5, 2, 60 union all

select 6, 2, 60 union all

select 7, 2, 60 union all

select 8, 3, 60 union all

select 9, 3, 450

select ROW_NUMBER() over (Partition by id2 order by id2) as rownum, * into Temp from @t

select *, new_val = case when rownum = 1 then value else (select SUM(value)

from Temp b

where b.rownum <= Temp.rownum

and Temp.id2 = b.id2)

end

from Temp

drop table Temp

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 8

saurabh.dwivedy (8/10/2009)
It would be instructive to familiarize yourself with Question Posting Best Practices. They do help get a speedier and more efficient response 🙂

Heh... good idea but if you're going to tell folks that, you have to give them a URL to find out how. 😉

--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)