How to Find the no of Consecutive Rows Having Same Data in a Particular Column......

Question

1.80/5 (2 votes)

See more:

Hi All....

I have a Requirement to Add the Value of a Column in 'n' Consecutive Rows in Sql Server.... Am Unable to do that in a Query......

The Requirement is like this.... Let's Say Table_Y I have......

    Item     OrderNo      Qty
    -----    --------    -----
1     X       OrderZx     10
2     X       OrderCV     20
3     X       OrderAX     100
.     .          .         .
.     .          .         .
n     X       OrderX      300 --Calculate The Sum Untill Here (10+20+100+...+300)
n+1   Y       OrderA      15
n+2   Y       OrderB      23
n+3   X       OrderC      10

The Task Here is I Have to Calculate the Sum of Qty For the X Item Untill OrderX(i.e. From OrderZx (1) to OrderX (n) All The Continous Orders For Item X).. I did it using While loop... But trying to do in d form of a query not in a loop or Cursor.... I Will get this Table Data on the fly... So Don't Know how Many Consecutive rows Will Have Same Item... i am thinking it can be acheived using Row_Number() But not Sure....
Please Note: The Output Required is: 10+20+100+...+300 (i.e.OrderZx(Qty)+OrderCV(Qty)+OrderAX(Qty)+...=OrderX(Qty) OrderX Should not be Included in Output...
Any Help Will be Appreciated...

Thanks
Raj

Posted 10-Jun-13 2:33am

Raja Sekhar S

Updated 10-Jun-13 20:40pm

v5

Add a Solution

Comments

Mike Meinz 10-Jun-13 9:30am

Your requirement is not clear. Item X is in OrderC. Why wouldn't OrderC's Qty of 10 be included in the SUM?

Raja Sekhar S 11-Jun-13 3:12am

Please Check the Comment i posted For your Answer Mike...

Zoltán Zörgő 10-Jun-13 9:37am

This is a question of reporting (subtotal), not of a single query. You should not try to merge a reporting task into a single statement. If you really need this in t-sql write a stored procedure or function and use cursors.

Raja Sekhar S 11-Jun-13 3:20am

Thanks for your Comment Zoltán Zörgő
I Want to use this in a Stored Procedure.... i Accomplished it using While Loop... But For the sake of Perfomance..... trying to write in d form of a query...

Zoltán Zörgő 11-Jun-13 3:47am

The question of performance is an other thing - but what is performance? Speed? Resource profile?
Making a query complicated - if not impossible - won't help you in making it performing. In such cases you can better optimize your code's resource consumption when using a stored procedure.

Raja Sekhar S 11-Jun-13 3:50am

I am trying to Increase the Speed...

Zoltán Zörgő 11-Jun-13 3:58am

Ok, and what have you done so far? What I see, is that you complain about other people's suggestions - but you have not shared your results.

Raja Sekhar S 11-Jun-13 4:22am

Oh.. Am Sorry For That...
What i have Done is that I used a While Loop to Calculate the Sum(Qty)


While(@Flag>1)
Begin
  Select Top(1) @Item=Item, @Qty=Qty,@Order=OrderNo From TableY where OrderNo not in (Select ID From fnSplitter(@CompletedOrders)) Order by Priority

 If @PrevItem=@Item
 Begin
  Select @TotalQty=@TotalQty+@Qty,@CompletedOrder=Case when @CompletedOrder='' Then
  @Order Else @CompletedOrder+@Order End
 End
 Else
  Break;
End

The Same Thing I used in a Cursor

 
Set @CalculateQtyCursor = Cursor Fast_Forward
For
Select Item, Qty From TableY Order by Priority

Open @CalculateQtyCursor 
Fetch Next From @CalculateQtyCursor  into @Item, @Qty
While @@fetch_status = 0  and @Item=isnull(@PrevItem,@Item)
Begin
 Select @TotalQty=@TotalQty+@Qty,@PrevItem=@Item
 Fetch Next From @CalculateQtyCursor  into @Item, @Qty
End

I am Sorry if i am Being Rude to any One or Offended Anyone.....

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

damodara naidu betha · Accepted Answer · 2013-06-11T01:17:00

Solution 4

Hi Raja Shekar,

 I assume you have a table Orders with following data.

    ITEM   ORDERNO   QTY
    --------------------
 X   ORdx    10
 X   Ordy    20
 X   Ordx    30
 Y   Ordx    20
 Y   Ordy    30
 X   OrdX    40
 X   OrdZ    50
 Z   ORdZ    100
 Y   Ordzy   8
 Y   OrdXy   9
 X   Ordxys  10

If you want to get a result set like the following,

     ITEM     QTY
   -----------------
     X    60
     Y    50
     X    90
     Z   100
     Y    17
     X    10

then try the following query on table Orders

SQL

WITH OrderCTE As (
 
   SELECT  ROW_NUMBER() OVER(ORDER BY (SELECT 1)) RowNum, ITEM,QTY FROM ORDERS
)
,ORDERQTYCTE AS
(
 
 SELECT  TOP 1 0 AS RowNumber,ITEM as Item,CAST(0 as Real) as Qty, 0 OrderLevel 
 FROM OrderCTE  
 
 UNION ALL
 
 SELECT RowNumber+1 AS RowNumber, OrderCTE.ITEM As Item, 
 (CASE WHEN OrderCTE.ITEM = ORDERQTYCTE.Item THEN ORDERQTYCTE.QTY+OrderCTE.QTY
 ELSE OrderCTE.QTY  END) AS Qty, 
 CASE WHEN OrderCTE.ITEM <> ORDERQTYCTE.Item THEN OrderLevel+1 
 ELSE OrderLevel END As OrderLevel
 FROM OrderCTE INNER JOIN ORDERQTYCTE 
 ON ORDERQTYCTE.RowNumber = OrderCTE.RowNum - 1
 
)
 
SELECT  Item,MAX(QTY) QTY FROM ORDERQTYCTE
GROUP BY OrderLevel,Item ORDER BY OrderLevel

Here have used CTE, Recursive CTE to get the desired result set.

I hope this solution helps you. Happy coding. :)

Posted 11-Jun-13 1:17am

damodara naidu betha

Updated 11-Jun-13 1:45am

v3

Comments

damodara naidu betha 11-Jun-13 7:37am

what is the data type of the "Qty" column in your table ? I have used "int" for this column in my table

Raja Sekhar S 11-Jun-13 7:40am

it's Real.... Thanks For the Quick Reply....

Raja Sekhar S 11-Jun-13 7:55am

Thank you Damodara Naidu... It's Working..... +5 for your Answer... Can u Suggest Where i can i learn about Recursive CTE's...

damodara naidu betha 11-Jun-13 7:47am

I have changed query according to your "Qty" columns's data type. Now it is running fine in my machine. Please let me know the status. If it is running fine then remove your first comment, please.

Raja Sekhar S 11-Jun-13 7:59am

It's Running Fine.... Deleted....

damodara naidu betha 11-Jun-13 8:02am

Thanks.

Raja Sekhar S 11-Jun-13 8:03am

I am Thankful to You Sir.... it's So humble of you... Can u Suggest me Where i can learn about recursive CTE's....

Mike Meinz 11-Jun-13 7:56am

This solution relies on the physical order of rows in the table. The order of rows in a table is nondeterministic. You can't rely that the order in which rows were inserted is the order in which they will be returned. For example, if rows are deleted and their free space is reused for newly inserted rows, the newly inserted rows will be in an area of the table prior to any recently inserted rows at the end of the table. The problem statement needs to specify the algorithm to select rows for summation based on columns available in the data. Using the RowNumber attribute will sometime yield incorrect results since rows inserted at the same time may not end up being adjacent to each other in the table.

damodara naidu betha 11-Jun-13 8:27am

Hi Mike. If it is temporary table then there is no question of deletion. Because OP is inserting
rows into the temporary table in a order (as he mentioned earlier )and generating result set by select query in a stored procedure. So Row_Number works fine here. So my solution can relay on the order of the rows in that temporary table.

Mike Meinz 11-Jun-13 8:32am

I still would never rely on the order of the rows since the SQL specification says that the physical order is nondeterministic.

Using a unique number for each row and then traversing consecutive rows leads to more complex code than just adding a column to the table that specifies which rows get summed together.

Raja Sekhar S 11-Jun-13 8:02am

Thank You For the Information Sir....
if i add one Column to the Table and Try to use it as Reference.... Will it Work...?

damodara naidu betha 11-Jun-13 8:04am

Sorry. I didn't get your question. Can you explain it?

Raja Sekhar S 11-Jun-13 8:09am

As Mike Sir Told.... It depends Based on Order of Rows... So While Inserting Data in Table Itself i will Try to Maintain a Unique No For That Row.... So we Can do this Based on the Unique No.... in ur code just Replacing Row_Number() Over(Order by (Select 1)) with Row_Number() Over(Order By(Column_Name))...?
Please Correct me if i am Wrong...

damodara naidu betha 11-Jun-13 8:14am

Actually you didn't provide your table structure. So I assumed that your table does't have any primary key column/ unique number column and wrote this query with Row_Number(Order By(Select 1)).If you have column in you table which maintains unique number for the rows, you can put that in Row_Number() OVER (Order By your column name). Try it and let me know the status.

Raja Sekhar S 11-Jun-13 8:17am

It's A temporary table what am using in Procedure... It don't Have any Pk's So Will Do That....

Mike Meinz 11-Jun-13 8:04am

I don't know what you mean.

Raja Sekhar S 11-Jun-13 8:11am

While Inserting Data in Table Itself i will Try to Maintain a Unique No For That Row.... So i Can do this Based on the Unique No....
Please Correct me if i am Wrong...

Mike Meinz 11-Jun-13 8:21am

Not a unique row number for each row. Instead, use a value that can be used to associate the rows that you want summed together. See my comment below concerning SumKey.

Mike Meinz 11-Jun-13 8:10am

If you add another column to table_y, that column should contain a value that could be used to associate the orders where the individual items should be summed.

Something like this:
SumKey ITEM ORDERNO QTY
--------------------
1 X ORdx 10
1 X Ordy 20
1 X Ordx 30
2 Y Ordx 20
2 Y Ordy 30
3 X OrdX 40
3 X OrdZ 50
4 Z ORdZ 100
5 Y Ordzy 8
5 Y OrdXy 9
6 X Ordxys 10

With a value like SumKey, my Solution 2 could be easily modified to produce the desired results accurately.

Raja Sekhar S 11-Jun-13 8:12am

Excellent Sir... Will Do that... By Doing Like This I can Directly Obtain the sum in a single Select Query....
Thanks For the Answer Sir....

Mike Meinz 11-Jun-13 8:18am

I have revised Solution 2 accordingly.

damodara naidu betha 11-Jun-13 8:30am

Excellent Mike. You removed complexity.

Mike Meinz · Accepted Answer · 2013-06-10T03:35:00

Solution 2

Revised after comment added to Solution 4 by OP (Raja Sekhar S):

SQL

SELECT SumKey,Item,Sum(Qty) FROM table_y GROUP BY SumKey,Item ORDER BY SumKey,Item

Posted 10-Jun-13 3:35am

Mike Meinz

Updated 11-Jun-13 2:18am

v6

Comments

RelicV 10-Jun-13 9:56am

OP is asking about consecutive rows, not all the records. The n+3th record has Item X and it should not be included.

Mike Meinz 10-Jun-13 10:06am

I think the Item X in the n+3 position in the example is a typo. I think the OP meant Z.

Neverthless, the definition of consecutive rows is unclear in the example provided in the question. We can't depend on the order of rows in a table to make decisions. When free space is re-used, inserted rows are not necessarily in a physical order that matches the chronological order.

Raja Sekhar S 11-Jun-13 1:34am

Thanks For the Reply Mike...
But this is not the Answer... i want the sum of Item X From 1 to 'n'... but i don't want 'n+3'..
I want the sum of Qty of Item X For only Consecutive Rows having Item x...
(+5 For your Answer...)
+5 for the answer.....
In the Exanmple Which i stated..... i want the sum of Qty of Item X From i to 'n' (i.e.. OrderZx to orderX).. But i don't want to include Orderc(i.e n+3 Row) Which Will be added if i use your Query...
If i use Your Query the output Will be 10+20+100+...+300+10 But i want Only this 10+20+100+...+300 as Output... The Definition of Consecutive Rows Here is: Only the rows Having Item X as Continously... Like...

 Row1 ItemX Qty
Row2 ItemX  Qty
Row3 ItemX  Qty
Row4 ItemX  Qty
Row5 ItemX  Qty
Row6 ItemY  Qty
Row7 ItemY  Qty
Row8 ItemX  Qty

in the Above Example The Consecutive rows having ItemX are 5 Rows...
So I want the sum of Qty For Five Rows.. ie.. Row1(Qty)+Row2(Qty)+Row3(Qty)+Row4(Qty)+Row5(Qty).... But i Don't want to include Row8(Qty)...
Hope This Gave u the Definiton of Consecutive Rows in this Particular Criteria....

Mike Meinz 11-Jun-13 6:33am

It is still unclear how consecutive rows are defined. You cannot rely on the physical order of rows in the table to make decisions. You must specify columns to be used in an ORDER BY or GROUP BY or some other method to select the rows to be processed from table_y. I still do not understand why ItemX for OrderC is not to be included. Just saying you don't want that one order's item X included does not explain why. Knowing why will help to define the correct algorithm to use.

Raja Sekhar S 11-Jun-13 8:21am

+5 For the Answer Sir... as a Token of Respect sir...

Mike Meinz 11-Jun-13 8:25am

Thank you.

I am still confused as to the business purpose of doing summation of quantities like this. In my experience, overly complex code is usually a signal that the business process is somehow flawed. That may or may not be the case here. I don't know because I do not understand why someone would want to do summation of consecutive items in such a manner.

Raja Sekhar S 11-Jun-13 8:35am

I asked the Person who has given me the assignment the same Thing When u posted the Comment...
But Din't get any Proper Answer... So am Unable to answer that Question sir...

Mike Meinz 11-Jun-13 8:41am

You have my sincerest sympathy!