Advanced Group By - 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 2005 Forums

Transact-SQL (2005)

Advanced Group By

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-01-12 : 16:50:50

Hi,

I have a list of values like this

b713dx
b436df
m12gh
m677kl
manchester
london
mk172hj

I want to take all values that contain a numeric value and count/group them by the prefix before the first number.

So in this case

B 2
M 2
MK 17

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-12 : 18:06:35

[code]DECLARE @Sample TABLE
(
Data VARCHAR(20)
)

INSERT @Sample
(
Data
)
VALUES ('b713dx'),
('b436df'),
('m12gh'),
('99z'),
('m677kl'),
('manchester'),
('london'),
('mk172hj')

SELECT SUBSTRING(Data, 1, Position - 1) AS Prefix,
COUNT(*) AS Items
FROM (
SELECT Data,
PATINDEX('%[0-9]%', Data) AS Position
FROM @Sample
WHERE Data LIKE '[a-z]%[0-9]%'
AND Data NOT LIKE '%[^a-z0-9]%'
) AS d
WHERE Position >= 2
GROUP BY SUBSTRING(Data, 1, Position - 1)[/code]

N 56°04'39.26"
E 12°55'05.63"

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-01-12 : 18:36:18

Thanks a lot!

It works great with the sample data, now i'm trying to add real data from the table but this gives an error.

DECLARE @Sample TABLE
(
Data VARCHAR(20)
)

INSERT @Sample
(
Data
)
VALUES (select searchitem from tblsearchlog)

SELECT SUBSTRING(Data, 1, Position - 1) AS Prefix,
COUNT(*) AS Items
FROM (
SELECT Data,
PATINDEX('%[0-9]%', Data) AS Position
FROM @Sample
WHERE Data LIKE '[a-z]%[0-9]%'
AND Data NOT LIKE '%[^a-z0-9]%'
) AS d
WHERE Position >= 2
GROUP BY SUBSTRING(Data, 1, Position - 1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 01:34:34

quote:
Originally posted by Mondeo

Thanks a lot!

It works great with the sample data, now i'm trying to add real data from the table but this gives an error.

DECLARE @Sample TABLE
(
Data VARCHAR(20)
)

INSERT @Sample
(
Data
)
~~VALUES~~ (select searchitem from tblsearchlog)

SELECT SUBSTRING(Data, 1, Position - 1) AS Prefix,
COUNT(*) AS Items
FROM (
SELECT Data,
PATINDEX('%[0-9]%', Data) AS Position
FROM @Sample
WHERE Data LIKE '[a-z]%[0-9]%'
AND Data NOT LIKE '%[^a-z0-9]%'
) AS d
WHERE Position >= 2
GROUP BY SUBSTRING(Data, 1, Position - 1)

you dont need values clause in insert...select

rajpes
Starting Member

13 Posts

Posted - 2010-01-13 : 02:03:08

quote:

Originally posted by Peso

DECLARE	@Sample TABLE
	(
		Data VARCHAR(20)
	)

INSERT	@Sample
	(
		Data
	)
VALUES	('b713dx'),
	('b436df'),
	('m12gh'),
	('99z'),
	('m677kl'),
	('manchester'),
	('london'),
	('mk172hj')

SELECT		SUBSTRING(Data, 1, Position - 1) AS Prefix,
		COUNT(*) AS Items
FROM		(
			SELECT	Data,
				PATINDEX('%[0-9]%', Data) AS Position
			FROM	@Sample
			WHERE	Data LIKE '[a-z]%[0-9]%'
				AND Data NOT LIKE '%[^a-z0-9]%'
		) AS d
WHERE		Position >= 2
GROUP BY	SUBSTRING(Data, 1, Position - 1)

N 56°04'39.26"
E 12°55'05.63"

Hi,Inserting into the table gives error, check syntax

.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ','.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 02:10:11

quote:

Originally posted by rajpes

quote:

Originally posted by Peso

DECLARE	@Sample TABLE
	(
		Data VARCHAR(20)
	)

INSERT	@Sample
	(
		Data
	)
VALUES	('b713dx'),
	('b436df'),
	('m12gh'),
	('99z'),
	('m677kl'),
	('manchester'),
	('london'),
	('mk172hj')

SELECT		SUBSTRING(Data, 1, Position - 1) AS Prefix,
		COUNT(*) AS Items
FROM		(
			SELECT	Data,
				PATINDEX('%[0-9]%', Data) AS Position
			FROM	@Sample
			WHERE	Data LIKE '[a-z]%[0-9]%'
				AND Data NOT LIKE '%[^a-z0-9]%'
		) AS d
WHERE		Position >= 2
GROUP BY	SUBSTRING(Data, 1, Position - 1)

N 56°04'39.26"
E 12°55'05.63"

Hi,Inserting into the table gives error, check syntax

.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ','.

it should be


INSERT	@Sample
	(
		Data
	)
SELECT 'b713dx' union all
SELECT 	'b436df'union all
SELECT 	'm12gh' union all
SELECT 	'99z' union all
SELECT 	'm677kl' union all
SELECT 	'manchester' union all
SELECT 	'london' union all
SELECT 	'mk172hj'

unless you're using sql 2008

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-01-13 : 07:59:58

Hi,

Thanks!

Just one question on the counting - the code below gives 3 but there are actually 4. Perhaps becuase the data can include duplicates. Can I fix this?

DECLARE @Sample TABLE
(
Data VARCHAR(20)
)

INSERT @Sample
(
Data
)
SELECT 'bb10 1ep' union all
SELECT 'bb10' union all
SELECT 'bb10' union all
SELECT 'bb9'

SELECT SUBSTRING(Data, 1, Position - 1) AS Prefix,
COUNT(*) AS Items
FROM (
SELECT Data,
PATINDEX('%[0-9]%', Data) AS Position
FROM @Sample
WHERE Data LIKE '[a-z]%[0-9]%'
AND Data NOT LIKE '%[^a-z0-9]%'
) AS d
WHERE Position >= 2
GROUP BY SUBSTRING(Data, 1, Position - 1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 08:02:46

whats the purpose of below condition

Data NOT LIKE '%[^a-z0-9]%'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-13 : 09:45:58

So that only records with digits 0-9 and characters a-z are valid.
Special characters like "! \ _" are automatically sorted out.

N 56°04'39.26"
E 12°55'05.63"

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-01-17 : 12:33:01

Hi,

Could anyone help as to why the count is slightly out in my example?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-17 : 15:22:47

Yes, if you explain how MK can have a count of 17...

N 56°04'39.26"
E 12°55'05.63"

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-01-17 : 16:14:38

Yes sorry, post 1 is incorrect - the results should be:

B 2
M 2
MK 1

If I run the example code you gave with the following sample data it returns 3 instead of 4

SELECT 'bb10 1ep' union all
SELECT 'bb10' union all
SELECT 'bb10' union all
SELECT 'bb9'

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-18 : 03:04:53

Yes, it because of the space. Include space in the clause visakh16 mentioned before.
Data NOT LIKE '%[^a-z 0-9]%'

N 56°04'39.26"
E 12°55'05.63"

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-01-18 : 05:11:12

Works perfectly - thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources