Odd Grouping Request

kilika · June 27th, 2003, 01:06 PM

I have some data that I need to group I currently group on severla columns

Lets say I have the following fields and data

Item Grade Type Weight Sequence
1 A RR 11.2 1
1 A RR 11.2 2
2 B RR 12.7 3
1 A RR 11.2 4

I currently group on Item, Grade, Type and Weight and get this

Item Grade Type Weight Sequence Count
1 A RR 11.2 1 (3)
2 B RR 12.7 3 (1)

(I just take the minimum Sequence number on the group by - I added the Count field so you could see how many records were grouped there. There are many other columns also involved, but they are just totaled up so there is really a reason for me grouping these records.)

I somehow need to order this by the sequence and then group if they are next to each other only. This is what it would look like:

Item Grade Type Weight Sequence Count
1 A RR 11.2 1 (2)
2 B RR 12.7 3 (1)
1 A RR 11.2 4 (1)

Can anyone help me accomplish this? I am not sure how I would go about this. We have an existing report that runs like this (Old code off of an AS400 IBM OS) and I need to reporduce the report in ASP. I have the report basically done and I just now discovered this SQL problem. I am anxiously awaiting your replies.

Thanks in advance

David Cameron · June 30th, 2003, 07:10 PM

I've been thinking about this problem a little and the best I have been able to come up with so far is this:

Code:

CREATE TABLE #Report
(
ReptID Int IDENTITY,
Grade char(1),
Type char(2),
Weight decimal(3,1),
Sequence Int
)

INSERT INTO #Report
(Grade, Type, Weight, Sequence)
SELECT Grade, Type, Weight, Sequence
FROM MyTable

SELECT ReptID, Grade, Type, Weight, Sequence
FROM #Report tR
WHERE NOT EXISTS (SELECT NULL
                  FROM #Report t2R
                  WHERE t2R.ReptID = tR.ReptID + 1
                      AND t2R.Item = tR.Item)

DROP TABLE #Report

This should return exactly what you want but without the COUNT column. If you want the count column I'll see if I can come up with something.

regards
David Cameron

defiant · July 10th, 2003, 02:36 AM

I think a rowset with the Type & Grade & Weight & Sequence is insufficient for kilika, since he (she) wants to 'group by' so some totals can be calculated.

here is my solution: (i hope this is not to late)

i assume that the data that needs grouping is in a table named T1. also, i assume that 'next to each other' is relative to sequence.

you need a supplementary column (i called it GroupNo of type int) in the T1 table.

code:

Code:

set nocount on

--change variable data types acording to your data types
declare @Item char(10), @Grade char(10), @Type char(10),
    @Weight char(10), @GroupNo int
declare @ItemLast char(10), @GradeLast char(10), @TypeLast char(10),
    @WeightLast char(10)

declare @First bit

declare crsT1 cursor local for
    select Item, Grade, Type, Weight
    from T1
    order by Sequence

open crsT1

fetch next from crsT1 into @Item, @Grade, @Type, @Weight

set @First = 1

while @@fetch_status = 0
begin
    if @First = 1
    begin
        set @GroupNo = 1
        set @First = 0
    end
    else if @Item <> @ItemLast or @Grade <> @GradeLast or @Type <> @TypeLast
        or @Weight <> @WeightLast
    begin
        set @GroupNo = @GroupNo + 1
    end

    update T1
    set GroupNo = @GroupNo
    where current of crsT1

    set @ItemLast = @Item
    set @GradeLast = @Grade
    set @TypeLast = @Type
    set @WeightLast = @Weight

    fetch next from crsT1 into @Item, @Grade, @Type, @Weight
end

select Item, Grade, Type, Weight, min(Sequence) as Sequence, count(*) as Count
from T1
group by Item, Grade, Type, Weight, GroupNo
order by min(Sequence)