SQL Grouping

kilika · May 20th, 2004, 05:27 PM

I need a consecutive line grouping algorithm. Lets say I have a database table that looks like this:

Code:

ID     Val1     Val2
1      A        10
2      B        20
3      B        30
4      C        40
5      B        50
6      B        60

I want to group it consecutively. When I group by Val1 using this statement:

Code:

select value1, sum(value2) from tbltestgroupings group by value1

I get:

Code:

ID     Val1     Val2
1      A        10
2      B        160
4      C        40

and what I want is:

Code:

ID     Val1     Val2
1      A        10
2      B        50
4      C        40
5      B        110

Does anyone know how I can accomplish this? This is very important and time critical, so if anyone has any help, it is appreciated.

Chris

melvik · May 21st, 2004, 11:31 PM

I dont understand u wanna grouping it & see ur B [u]twice</u>?! If u tell why u r gonna do that I may help u.

Always:),
Hovik Melkomian.

kilika · May 24th, 2004, 11:16 AM

It is a schedule of sorts. The schedule cannot change order (hence needing two B's), but I can group parts together when they occur together in sequence.

Chris

kilika · May 24th, 2004, 11:27 AM

Maybe the part where I said I "Can" group parts is not correct. I "have" to group them. I am replacing an existing system and this relly is just a chunk in the middle. I cannot change what comes in to me (As far as data) adn I cannot change how it goes on to the next process. Right now it is done with Excel and a bunch of complicated Macros and is relaly not a good setup. The reason the B is there twice is it just groups concurrent records of like data together and sums their values. Any help anyone could offer would be so greatfully received.

Chris

chrislepingwell · May 24th, 2004, 11:47 AM

Chris, I'm not sure you can actually do what you're after using SQL (without some very dirty cheating). It might be that using Excel VBA (or another programming language) is the better method - the task seems more suited to a procedural function that a set-based one. To do this in SQL would essentially entail building a while-wend loop running off the ID.
How are you getting your raw data? Batch feed, line feed or something else entirely? We might be able to come up with a brilliant left-field idea if we know more about the process :) Perhaps if you are able to add a sequence indicator to the incoming data. i.e. something that increments each time Value1 changes. That way you would be able to add that to your group by clause and get what you want

Chris

There are two secrets to success in this world:
1. Never tell everything you know

kilika · May 24th, 2004, 11:51 AM

The data comes to me in a SQL table. I merely need this grouping for showing the schedules on an intranet, and also for running queries against. The grouping is to get the data in abetter format for me to use with a much larger scale of SQL Server's capabilities.

Chris

chrislepingwell · May 24th, 2004, 12:06 PM

OK, I'm still thinking that adding your own sequence indicator is the way to go, all we need to do is work out the best strategy for putting it there.
Do you get this table as a discrete object, or does it get continuously updated and you just query it as and when required? In the first instance, your best option is going to writing SQL looping code to add an indicator, in the second, you should be able to set up a trigger to do it.
The only other option I can think of probably involves correlated subqueries and similar high-order SQL magic ;) Where's Jeff Mason when you need him!

Chris

There are two secrets to success in this world:
1. Never tell everything you know

happygv · May 25th, 2004, 05:01 PM

Chris,

Jeff is missing for a long time. I too was in search of him. And he posted one or two after that and again went on sleep mode. Hope he should be travelling. But how long? Come on Jeff, guys really need you at this time.

Cheers!

_________________________
-Vijay G

Strive for Perfection

Jeff Mason · May 26th, 2004, 06:08 AM

Sorry for my absence, guys.

I haven't been traveling, but rather we've been engaged with a new client who dares to place somewhat unreasonable demands on our time. Life has gotten a bit crazy right now due to the workload, so I've had very little time for participating in p2p. Sorry, but believe me, if you are going to have problems, too much work is the sort of problem to have, considering the alternatives. :D

I've taken a quick look at the OP's problem and no set based solution jumps out at me. If I understand the problem correctly, the requirement that the rows of the table be processed in ID order so that the desired "consecutive grouping" can be obtained precludes that. I'm not really sure what "consecutive grouping" is, but whatever it is, it's not a set operation...

I'm *very* suspicious of this (table) design...

I can't believe I'm suggesting this, but perhaps a cursor based solution would work. Process the rows one at time, summing the Val2 column until the Val1 column changes, insert the result into temporary table (or table variable if SQL2000), and SELECT the temporary to return the final resultset.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

kilika · May 26th, 2004, 11:23 AM

Maybe I can explain it better. Your solution may be my only option anyway. I work at a Steel Mill. These records are a list of products we are going to create. Each product may occur several times a year. At each interval of a p[roduct, we have several different lengths. At each interval where we create a product at a certain length, we may have several orders listed together. I need to group it at the order level. It will still need to be listed by interval and product and length. The report and functionality is built around product and the interval and length(which is why I need the product to be grouped by product and interval and length). The report has to stay in the same order (obviously). Thes data is in a table on another system (IBM AS400) and is then mirrored over to me. I have no control over the data, and I cannot change it. I could create another table or a temp table to join to it though.

Chris