Group within a column *** - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group within a column ***

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-08-31 : 06:27:37

Hi SQL friends,

I'm curious whether SQL can easily do grouping within a column or not.
For example:

Create table TESTTABLE
( Item varchar(20),
Qty integer
)

insert into TESTTABLE
select 'ar-adsds-01', 12 union all
select 'ar-adsds-02', 20 union all
select 'ad-ldssk-03', 14 union all
select 'ad-dsadd-32', 500

i want to group by column Item (by string before the first -) as :

ar 32
ad 514

by the way, im using SQL2003, not yukon.
do i need to apply regularexpression using contains??
or is there any other simpler ways?
thanks for your kind attention.
SQL rocks..

... sql is fun...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 06:46:07

First of all, there is no such thing as SQL2003.
Second, this will do the trick for you

-- Prepare test data
DECLARE	@Test TABLE (Item VARCHAR(20), Qty INT)

INSERT	@Test
SELECT 'ar-adsds-01', 12 UNION ALL
SELECT 'ar-adsds-02', 20 UNION ALL
SELECT 'ad-ldssk-03', 14 UNION ALL
SELECT 'ad-dsadd-32', 500 

-- Do the work

-- This will work, as long as there are 3 parts in the Item name
-- Parts can be of any length greater than 0
SELECT		PARSENAME(REPLACE(Item, '-', '.'), 3),
		SUM(Qty) 
FROM		@Test
GROUP BY	PARSENAME(REPLACE(Item, '-', '.'), 3)

-- This will work, as long as there are at least 2 characters in the Item name
SELECT		LEFT(Item, 2),
		SUM(Qty) 
FROM		@Test
GROUP BY	LEFT(Item, 2)

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-31 : 10:06:50

[code]select left(Item, 2), sum(Qty)
from TESTTABLE
group by left(Item, 2)[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 10:17:22

Oh, sorry! Did you emphasis the change from @test table to TESTTABLE table?

Peter Larsson
Helsingborg, Sweden

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-08-31 : 11:20:59

Opz..did I mention 2003? lol..
It's a good solution indeed, however we need to takecare of NULL, or item doesnt have - (hyphen), or not exactly 3parts.

the itemcode may be
ABS0990192
ABD-JAJS
ABD-KSKL-01929
ABD-KSKL-01929-B

so basically, if I only want to group by string (regardless of the length) before -, can the code does the trick??

THx..

... sql is fun...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-31 : 11:28:20

The first new sample does not conform to having "-". How to deal with that?
Also, look at the other two solutions provided by me and khtan.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-31 : 11:41:43

try this

select 	left(Item, case when charindex('-', Item) <> 0 then charindex('-', Item) - 1 else len(Item) end),
	sum(Qty)
from	TESTTABLE
group by left(Item, case when charindex('-', Item) <> 0 then charindex('-', Item) - 1 else len(Item) end)

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-08-31 : 12:04:49

well..this solution makes more sense to me..
will try out tomorrow..see how it goes......

ohya by the way I have another topic to solve.
Can SQL does recursive loop?

Thx both Peso and Khtan for your helps.
I appreciate that.

... sql is fun...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources