Query with aggregate Function

Question

Post reply

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: 1000539 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://qa.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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 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://qa.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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 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)
Intro to Tally Tables and Functions

Saurabh Dwivedy SSCommitted Points: 1885 More actions · Answer 5

Saurabh Dwivedy

SSCommitted

Points: 1885

August 10, 2009 at 9:54 pm

#1037211

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: 1885 More actions · Answer 6

Saurabh Dwivedy

SSCommitted

Points: 1885

August 10, 2009 at 10:01 pm

#1037213

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: 1885 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: 1000539 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)
Intro to Tally Tables and Functions