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

General SQL Server Forums

New to SQL Server Programming

group by

Author

Topic

svicky9
Posting Yak Master

232 Posts

Posted - 2005-09-15 : 00:55:15

Hi friends

I have a table like

col A col B
V112333 saasfffdd
V113134 asfdserer
V112454 ldjfldjkd
V113232 dfdfdfmmm
V112545 xbvxbxbbb

.... and so on there are 100 records

I want to group by colA and particular the first four letters like V112,V113 and so on....is there anyway i can group by the data inside a col

cheers
Vic

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 01:11:17

Post your expected result

Select left(ColA,4) from yourTable group by left(ColA,4)

Madhivanan

Failing to plan is Planning to fail

svicky9
Posting Yak Master

232 Posts

Posted - 2005-09-15 : 01:26:44

My expected result should be like

V1121322 sgdsggds
V1121422 dfdsfdf
V1121872 dfdfdfdf

V1131444 gsgfsgfg
V1135555 fdfdsfdf
V1134545 aafdfdfd

V1143443 adfdfdfd
V1142323 afdrerer

it should group by the first 4 letters in the first col

thank you

vic

Vicky

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 01:34:06

From the output, it seems that you need only order by

Select ColA, ColB from yourTable order by ColA

Madhivanan

Failing to plan is Planning to fail

svicky9
Posting Yak Master

232 Posts

Posted - 2005-09-15 : 05:10:46

Suppose i have third col as is u can see below and i want to add the third col based on the first 4 letters

V112333 saasfffdd 1
V113134 asfdserer 2
V112454 ldjfldjkd 1
V113232 dfdfdfmmm 2
V112545 xbvxbxbbb 2

I should get the result like this

V112333 saasfffdd 4
V112454 ldjfldjkd
V112545 xbvxbxbbb

V113134 asfdserer 4
V113232 dfdfdfmmm

Vic

Vicky

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 05:25:22

Where do you want to show these data?
Are you using reports for this?

Madhivanan

Failing to plan is Planning to fail

svicky9
Posting Yak Master

232 Posts

Posted - 2005-09-15 : 05:40:39

Hi Madhivanan

I am trying to create a report on that using T-sql in Query analyzer
I am trying to add the third column based on the first four digits in the first col.

In the first col the first four variables represent a dept and the next 4 represent the ID.
So i want to perform the sum of the third col based on the dept

cheers
Vic

Vicky

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 07:14:03

I am not sure whether this works


Select T1.col1, T1.col2, T2.i from yourTable T1 inner join 
	(
		Select left(col1,4) as Col1, sum(col3) as i from yourTable group by left(col1,4)
	) T2
on left(T1.col1,4)=left(T2.col1,4) order by T1.col1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources