Group by

Last Post 05 Oct 2007 02:43 PM by nosepicker. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Australia2
New Member
New Member

--
04 Oct 2007 07:31 PM
Hi Listers,
Can some one assist me with a solution using Group by?

ColA ColB ColC ColD

AAA 0 1 NULL
AAA 1 2 NULL
AAA 2 3 NULL
AAA 25 26 0.2
AAA 26 27 0.2
AAA 27 28 0.2
AAA 28 32 5.6

What i'm trying to achieve is to create the following:

AAA 0-3 NULL
AAA 25-28 0.2
AAA 28-32 5.6

TIA

Rgds,
John
SQLUSA
New Member
New Member

--
05 Oct 2007 06:43 AM
select ColA,
min(ColB),
max(ColC),
avg(ColD)
from TableX
group by ColA

You may have to include ColD in the group by...depends on your goal....
nosepicker
New Member
New Member

--
05 Oct 2007 02:43 PM
Something like this, perhaps:

SELECT colA, CONVERT(varchar, MIN(colB)) + '-' + CONVERT(varchar, MAX(colC)), colD
FROM YourTable
GROUP BY colA, colD
Australia2
New Member
New Member

--
07 Oct 2007 01:29 PM
Thx SQLUSA,

Ur almost there! I shld also have included a couple of extra lines to demonstarte my problem:
I need to use ColD in the group by, coz I dont want to avg the total of ColD

AAA 0 1 NULL
AAA 1 2 NULL
AAA 2 3 NULL
AAA 25 26 0.2
AAA 26 27 0.2
AAA 27 28 0.2
AAA 28 32 5.6
AAA 32 36 -0.01
AAA 36 44 0.2

The problem occurs when a like value for ColD occurs at a further interval. eg @ 36 - 44, so a min, max ends up like:

AAA 0-3 NULL
AAA 25-44 0.2 --> the group by returns me an overlapping interval
AAA 28-32 5.6
AAA 32-36 -0.01

but what I need is:

AAA 0-3 NULL
AAA 25-44 0.2
AAA 28-32 5.6
AAA 32-36 -0.01
AAA 36-44 0.2

Hope this makes some sense!

TIA
John


SwePeso
New Member
New Member

--
08 Oct 2007 01:23 AM
-- Prepare sample data
CREATE TABLE #Sample
(
ColA VARCHAR(3),
ColB TINYINT,
ColC TINYINT,
ColD SMALLMONEY,
Sequence INT
)

INSERT #Sample
(
ColA,
ColB,
ColC,
ColD
)
SELECT 'AAA', 0, 1, NULL UNION ALL
SELECT 'AAA', 1, 2, NULL UNION ALL
SELECT 'AAA', 2, 3, NULL UNION ALL
SELECT 'AAA', 25, 26, 0.2 UNION ALL
SELECT 'AAA', 26, 27, 0.2 UNION ALL
SELECT 'AAA', 27, 28, 0.2 UNION ALL
SELECT 'AAA', 28, 32, 5.6 UNION ALL
SELECT 'AAA', 32, 36, -0.01 UNION ALL
SELECT 'AAA', 36, 44, 0.2

CREATE CLUSTERED INDEX IX_Sample ON #Sample (ColA, ColB)

-- Initialize staging
DECLARE @ColA VARCHAR(3),
@ColD SMALLMONEY,
@Sequence INT

SELECT TOP 1 @ColA = ColA,
@ColD = ColD,
@Sequence = 0
FROM #Sample
ORDER BY ColB

-- Do the staging
UPDATE #Sample
SET @Sequence = Sequence = CASE WHEN @ColA = ColA AND (@ColD IS NULL AND ColD IS NULL OR @ColD = ColD) THEN @Sequence ELSE @Sequence + 1 END,
@ColA = ColA,
@ColD = ColD


-- Show the expected output
SELECT ColA,
MIN(ColB) AS ColB,
MAX(ColC) AS ColC,
ColD
FROM #Sample
GROUP BY Sequence,
ColA,
ColD
ORDER BY ColA,
Sequence

-- Clean up
DROP TABLE #Sample
You are not authorized to post a reply.

Acceptable Use Policy