[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Query - Group By with Like

Posted on 2013-05-15
10
Medium Priority
?
298 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 7

Assisted Solution

by:Ross Turner
Ross Turner earned 400 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 500 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 70

Accepted Solution

by:
Scott Pletcher earned 1000 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 100 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 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