Identifying similar data and grouping

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)

Identifying similar data and grouping

Author

Topic

inversions
Starting Member

5 Posts

Posted - 2007-11-07 : 11:54:47

Hi,

I was wondering if anyone could help me with (a probably noobish) problem im having. I've tried googling about but i dont have enough knowledge of sql to put what i want into the correct syntax for searching.

my problem is this:

i have a table which contains data like follows:

ID | Hits | Date
----------------------------------------
AA12345678 25 2007-05-05
AA12345678 100 2007-05-07
AA12637287 125 2007-03-12
AA82732870 60 2007-01-19
AB93209328 76 2006-09-07
CD89383839 200 2005-10-13
AA03943984 87 2007-10-23
JK83829382 10 2007-12-29

i want to be able to sum up all the data for AA,AB,CD etc so its outputs like this:

ID |Hits
----------
AA 387
AB 76
CD 200
JK 10

taking all the hits for any ID that begins with 'AA' and adding them, working it out for each new letter type.

Initially i was thinking some sort of if or case statement however the problem is there is so many combinations of the first two letters in ID that it would be to much to hardcode and compare. I was wondering if there is a smart way to do this within sql statement itself.

Many thanks,

Inversions

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-07 : 11:58:38

assuming the left two characters are always the identifier you can try this...
select
LEFT(ID, 2) as id_that_should_be_in_separate_column --just poking fun
,SUM(Hits)
from
badly_designed_table --still poking fun
group by
LEFT(ID, 2)

inversions
Starting Member

5 Posts

Posted - 2007-11-07 : 12:20:53

hi anonymous1,

That did the trick, thanks a lot. I had something similar using left() before but I think I just didn’t put it in the correct place within the statement (as i had a inner join in the from clause and other where conditions as well)

unfortunately ( or probably fortunately

) i didn’t design the db or table, was just given it and told "go do this." So its an opportunity to learn something new and good to know that there’s good support out their for such things like this place.

thanks for taking some of your time to help me out with it, i really appreciated it.

have a good day,

inversions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources