Solved

SQL Query - Group By with Like

Posted on 2013-05-15
10
296 Views
Last Modified: 2013-05-16
Good Day Experts!

I have a situation that I am not sure how to approach.

My query:

Select count([Pro Number]) as myCount From Customer
Group By [Pro Number]
Having count([Pro Number]) > 1

So, I get [Pro Number] values that are duplicates.

I would like to get the following to be considered duplicates:
123456789
CAR123456789

Is this possible?

Thanks,
jimbo99999
0
Comment
Question by:Jimbo99999
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39167741
Are the values always prefixed with CAR if they have anything? Some examples would be helpful. If there is a consistent difference then it is as easy as replacing the CAR with an empty string.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39167765
The challenge is not skipping the character data, that is trivial to code (although probably a dog to run), the problem is actually reporting the original [Pro Number].  However from looking at your query you do not include it so you may not need it.  All you need is a function (could even be a CLR) to lose the character data, so that your query looks like this:
SELECT  COUNT(*) AS myCount
FROM    Customer
GROUP BY dbo.udf_GetNumericOnly([Pro Number])
HAVING  COUNT(*) > 1

Open in new window

0
 

Author Comment

by:Jimbo99999
ID: 39167775
Thanks for responding.  Yes, the prefix will always be the same like the above example.  I am trying to catch duplicates with this keying error.  I just can't seem to figure out the SQL syntax to add to my query.
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 7

Assisted Solution

by:Ross Turner
Ross Turner earned 100 total points
ID: 39167855
I don't know if i missing a trick but couldn't you do this

Create a rank based off ProNumbers without the preceding CAR and Rank them.
If it over 1 it's a duplicate.

http://sqlfiddle.com/#!3/24d1f/22

Select * From 
(
Select 
* ,
Rank() over (partition by replace(ProNumber,'CAR','') order by ProNumber) as Ranked
From 
Customer
) As TEST 
Where Ranked > 1

Open in new window

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
ID: 39168206
Yes, the prefix will always be the same like the above example.
Then it should be as simple as:
SELECT  COUNT(*) AS myCount
FROM    Customer
GROUP BY SUBSTRING([Pro Number], 4, 50)  -- Change 50 as appropriate
HAVING  COUNT(*) > 1

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39168446
Can you provide some sample data and your expected results?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 39169107
Select
    CASE WHEN LEFT([Pro Number], 1) <> 'C' THEN 'CAR' ELSE '' END + [Pro Number] AS [Pro Number],
    Count([Pro Number]) as myCount
From dbo.Customer
Group By CASE WHEN LEFT([Pro Number], 1) <> 'C' THEN 'CAR' ELSE '' END + [Pro Number]
Having count([Pro Number]) > 1


for example:

Select
    CASE WHEN LEFT([Pro Number], 1) <> 'C' THEN 'CAR' ELSE '' END + [Pro Number] AS [Pro Number],
    Count([Pro Number]) as myCount
From (
    select '123456789' as [pro number] union all
    select 'CAR123456789'
) AS test_data
Group By CASE WHEN LEFT([Pro Number], 1) <> 'C' THEN 'CAR' ELSE '' END + [Pro Number]
Having count([Pro Number]) > 1
0
 

Author Comment

by:Jimbo99999
ID: 39169117
Ok, back online.

I have a little more background for the request I was given.  This stems from duplicate keyed values into the system.  The current report I have written finds "exact matches" on the value.  It has come to light, that a particular situation occured where an alpha value is being typed prior to the numeric value.

So, Keyer1 enters order with invoice 123456.  Then, unknowinlgy Keyer2 enters same order for invoice 123456 but puts CARS in front of it due to a misunderstanding in the training process.

I am tasked with finding all of the values in this scenario.  I have to find the following:

123456
CARS123456

987654
CARS987654

But not all CARSxxxxxx will have a corresponding xxxxxx value.

"Thinking through this I need to:"
1)Find all values with CARS in front
2)Then take the numeric value after the CARS and see if that is a value found in the table
3)If 1) and 2) satisfied, then display both on duplicate report
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 25 total points
ID: 39169427
see this.
SELECT * FROM (
SELECT  *,COUNT(*) OVER (PARTITION BY SUBSTRING([Pro Number], 4, 50)) Cnt
FROM    Customer) t1
WHERE cnt > 1

Open in new window

0
 

Author Closing Comment

by:Jimbo99999
ID: 39171299
The "little" project has been cancelled.  I appreciate all the responses and have awarded points for your efforts.  In my spare time I will still try to get it working for future reference.

Thanks again,
jimbo99999
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

705 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