CODEWITHSUNDEEP
sqlsql-servercountgroup-bycase
user240179
user240179

Reputation: 39

SQL Count similar records

Let's say I have not user-friendly table called Animals. Like so:

ID NAME CHANGED
1  Dog  2016-01-02
2  Dog  2016-01-05
3  Dog2 2016-01-05
4  Dog3 2016-01-06
5  Cat  2016-01-06
6  Fish 2016-01-07
7  Cat2 2016-01-07

And I want to count how many similar words are in the table that begins the same way but could end different. Also I would only want to show just Names without additional '2' or '3'. To be more precise I want such result:

Counted NAME
4       Dog
2       Cat
1       Fish

And here is the code I've been trying to get along with ( have tried many ways ):

SELECT COUNT(
CASE
    WHEN NAME LIKE '%' + NAME + '%' THEN 1
        ELSE 0
END
) AS Counted, NAME FROM Animals
WHERE NAME LIKE '%' + NAME + '%' AND
    NAME NOT LIKE '%2' AND
    NAME NOT LIKE '%3'
    GROUP BY NAME
    ORDER BY Counted DESC

Upvotes: 0

Views: 168

Answers (4)

Pரதீப்
Pரதீப்

Reputation: 93734

You have to define the animals category in separate table this will never work out. Grouping will be very difficult with different spelling for same animal.

If it is always numbers at the end of animals name then

select left(Name,isnull(nullif(patindex('%[0-9]%',Name)-1,-1),len(Name))),count(1)
From yourtable 
Group by left(Name,isnull(nullif(patindex('%[0-9]%',Name)-1,-1),len(Name)))

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146541

select case when patIndex('%[0-9]', val) = 0 then val  
    else substring(val, 1, patIndex('%[0-9]', val)-1) end,  count(*)
from table
Group by case when patIndex('%[0-9]', val) = 0 then val  
    else substring(val, 1, patIndex('%[0-9]', val)-1) end

Upvotes: 1

iamdave
iamdave

Reputation: 12243

This will remove all numbers from your data, regardless of how many there are or where they are in your data. It is a bit ridiculous, but then sometimes SQL just is...

declare @t table(ID int, Name nvarchar(10), Changed date);
insert into @t values
 (1  ,'Dog','20160102')
,(2  ,'Dog','20160105')
,(3  ,'Dog2','20160105')
,(4  ,'Dog30','20160106')
,(5  ,'Cat','20160106')
,(6  ,'Fish','20160107')
,(7  ,'Cat27676','20160107');

select count(1) as Counted
        ,replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(Name
            , '0', ''),'1', ''),'2', ''),'3', ''),'4', ''),'5', ''),'6', ''),'7', ''),'8', ''),'9', '') as Name
from @t
group by replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(Name
            , '0', ''),'1', ''),'2', ''),'3', ''),'4', ''),'5', ''),'6', ''),'7', ''),'8', ''),'9', '')
order by Counted desc;

Output:

+---------+------+
| Counted | Name |
+---------+------+
|       4 | Dog  |
|       2 | Cat  |
|       1 | Fish |
+---------+------+

Upvotes: 0

Anand
Anand

Reputation: 1123

Tedious but simple method is to strip your data off numeric characters:

select new_name,
       count(*) as counted
from   (
       select replace(replace(replace....replace(Name, '0', ''), '1', ''), '2','')... as new_name
       from   Animals
       ) as a
group by new_name

Upvotes: 1

Related Questions