Query - 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

Query

Author

Topic

PeteA
Starting Member

8 Posts

Posted - 2010-09-27 : 16:24:56

I have the following values (in theory)

ID / Num
1234-2 / 3
1234-5 / 2
1234-2 / 4
12345-7.5 / 4
12345-4 / 41
123456-2 / 2
123456-9 / 8
123456-21.5 / 3

Now what I want to try and get my head round is how I can add the values together based on the information in the ID column. Each ID has a - near the end and then a value which can be any length. Some times not a number, can be ABC or even ABC123 for example.

So in effect I would get the following from the output:

1234 / 9
12345 / 45
123456 / 13

Addition is not the problem but how do I ignore everything before the last -, In theory the database could have 1000's of ID's. So doing a search based on LIKE would not be efficient. However if I can automatically search the list comparing values, it's going to save me a lot of time.

SELECT SUM(qty)
FROM TableOne
WHERE (id LIKE '1234%')

Thats what I can use at the moment. However I cant seem to get my head round how to replace the like and make it search through the whole lot. I only really need 2 output columns which would be the qty and the grouped id.

Hope this makes sense.

If someone could explain or show me a resource that would help me I'd rather that than just a straight query. I'd rather learn and understand rather than be given something.

He who said nothing was impossible, never tried slamming a revolving door.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2010-09-27 : 18:02:50

I think this may work:

declare @temp table (id varchar(20), num int)

insert @temp (id, num)
select '1234-2', 3 union
select '1234-5', 2 union
select '1234-2', 4 union
select '12345-7.5', 4 union
select '12345-4', 41 union
select '123456-2', 2 union
select '123456-9', 8 union
select '123456-21.5', 3

select id, sum(num) 
from (
    select substring(id, 1, charindex('-', id)-1) as id, num 
    from @temp) a
group by id

I took out the part of the id before the dash and then grouped the sum of the num by each id. Does it make sense?

Edit: took out the unnecessary conversion of the id to int

Some days you're the dog, and some days you're the fire hydrant.

PeteA
Starting Member

8 Posts

Posted - 2010-09-29 : 15:50:07

Thanks for helping me, and sorry for the late reply

Ok so I've done some extra reading and I'm not quite sure why union is at the end of each select in the table. If I didn't know the list of ID's or NUM but knew the column names how would I then go about using union.

Just been playing around with the different ID's I have and it seems if I have an ID with two - in them it seems to cause a problem. So for example 12345-67-8 and 12345-68-8 will be merged because it takes the 1st - and not the last -.

I have tried using reverse on the charindex to no avail.

If I could sort the - issue. Is it possible to query straight from table. I have tried modifying the select but to no avail.

He who said nothing was impossible, never tried slamming a revolving door.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-30 : 12:07:28

that was just a illustration of your scenario. actually you need only last select which is the solution.
there's one problem with solution though. If there are ID values without any - at all, it will break. so to be safe, its better to use


select id, sum(num) 
from (
    select substring(id, 1, case when charindex('-', id)>0 then charindex('-', id)-1 else len(id) end) as id, num 
    from @temp) a
group by id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

PeteA
Starting Member

8 Posts

Posted - 2010-09-30 : 14:20:06

That makes more sense now, I've modified it so it works with the table I'm using and the values, Still doesn't work with id's with more than one dash.

I'm still a little unsure on how I would only take the last - because as previously mentioned some of the id's have multiple -'s.

Are there any websites you would suggest for this kind of query? So I might learn the answer?

He who said nothing was impossible, never tried slamming a revolving door.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2010-09-30 : 19:43:11

This should include everything before the last dash:

select id, sum(num) 
from (
	select case 
		when charindex('-', id) > 0 
			then reverse(substring(reverse(id), charindex('-', reverse(id))+1, len(id)))
		else id 
	end as id, num
    from @temp) a
group by id

If that doesn't work, please post all the different variations of your data and your expected results and we'll see what we can do.

Some days you're the dog, and some days you're the fire hydrant.

PeteA
Starting Member

8 Posts

Posted - 2010-10-01 : 16:33:41

Fantastic that works.

So just so I make sure I understand this correctly.

When a - is found (greater than zero)
* Reverse the string, find the fist - and use all characters from beyond that point. Or to put it another way start from that point.
* Then reverse the string back to get it in the correct dirction
*** Not sure why the +1 is there or why len is needed? Could you explain please

*else just print id
*group all id's together and add all 'num' together

Thanks very much for everyone help.

He who said nothing was impossible, never tried slamming a revolving door.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources