Solved

SQL Query - Group By with Like

Posted on 2013-05-15
10
293 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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 40

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Vb.net threads keep increasing 2 35
SQL Activity Monitor detail 2 24
Requesting help with creating an SQL query with 2 tables 6 24
First Max value 3 27
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 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