Solved

Select and Group by SubString

Posted on 2010-11-30
2
727 Views
Last Modified: 2012-05-10
My attached code produces output as follows below
BusType       DealID
LT                  8001
LT                  8001
LT                  8001
SS                 1600
SS                 1600
SS                 1600

How do I modify my original select statement so I get the output below?
I'd rather not use a temp table
BusType       DealID
LT                  8001
SS                 1600

Declare @OrdTranID int

Set @OrdTranID = 9001



Select	Left(afsSource,2) as busType,

		SubString(afsSource,4,4) as DealID	

from	proc_cfa.dbo.P_AvailableForSale

where	afsTransAction = @OrdTranID

Open in new window

0
Comment
Question by:lrbrister
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
this should do:
Declare @OrdTranID int

Set @OrdTranID = 9001



Select  Left(afsSource,2) as busType,

                SubString(afsSource,4,4) as DealID      

from    proc_cfa.dbo.P_AvailableForSale

where   afsTransAction = @OrdTranID

group by Left(afsSource,2), SubString(afsSource,4,4)

Open in new window

0
 

Author Closing Comment

by:lrbrister
Comment Utility
Great,
Thanks...I had a typo in my efforts.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article I will describe the Backup & Restore 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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now