[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Group By Sum with Substring SQL Syntax

Posted on 2010-04-09
4
Medium Priority
?
393 Views
Last Modified: 2012-05-09
I have a situation where I need to total by similar but not exact strings. Example data:

Cust ID    Val 1    Val 2    Val 3
448          20        30         40
448-01     30        40         50
448-02     60        70         80

What I want is one line totalling for 448 to be 110,140 and 170
The dash may not be in the third postion so I want to locate its position and then total the group.
My code below does not work because there are customer ID's that have no dash and the query bombs. How can I adjust the structure to produce the desired results.
select sum(agperamt_1),sum(agperamt_2),sum(agperamt_3),sum(agperamt_4),sum(onordamt)
from rm00103
where custnmbr like '448%' 
group by left(custnmbr,patindex('%-%',custnmbr)-1)

Open in new window

0
Comment
Question by:rwheeler23
  • 3
4 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 30236447
select sum(agperamt_1),sum(agperamt_2),sum(agperamt_3),sum(agperamt_4),sum(onordamt)
from rm00103
where custnmbr like '448%'
group by case when charindex('-', custnmbr) = 0 then custnmbr else left(custnmbr,patindex('%-%',custnmbr)-1) end
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 30236863
try this one

select sum(agperamt_1),sum(agperamt_2),sum(agperamt_3),sum(agperamt_4),sum(onordamt)
from rm00103
group by left(custnmbr,case patindex('%-%','448') when 0 then LEN('448') else patindex('%-%','448')-1 end)

Open in new window

0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 30236921
select sum(agperamt_1),sum(agperamt_2),sum(agperamt_3),sum(agperamt_4),sum(onordamt)
from rm00103
where custnmbr like '448%'
group by left(custnmbr,case patindex('%-%','448') when 0 then LEN('448') else patindex('%-%','448')-1 end)
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 30237006
sorry, this one is correct:

select sum(agperamt_1),sum(agperamt_2),sum(agperamt_3),sum(agperamt_4),sum(onordamt)
from rm00103
where custnmbr like '448%' 
group by left(custnmbr,case patindex('%-%',custnmbr) when 0 then LEN(custnmbr) else patindex('%-%',custnmbr)-1 end)

Open in new window

0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question