CODEWITHSUNDEEP
sqloracle-database
johnny
johnny

Reputation: 19735

How can an get count of the unique lengths of a string in database rows?

I am using Oracle and I have a table with 1000 rows. There is a last name field and

I want to know the lengths of the name field but I don't want it for every row. I want a count of the various lengths.

Example:

lastname:

smith
smith
Johnson
Johnson
Jackson
Baggins

There are two smiths length of five. Four others, length of seven. I want my query to return

7
5

If there were 1,000 names I expect to get all kinds of lengths.

I tried,

Select count(*) as total, lastname from myNames group by total

It didn't know what total was. Grouping by lastname just groups on each individual name unless it's a different last name, which is as expected but not what I need.

Can this be done in one SQL query?

Upvotes: 9

Views: 25638

Answers (4)

Robert Every
Robert Every

Reputation: 1

This is what you want (for Microsoft SQL Server, Oracle syntax will be similar).

select len(ColumnName) as [Len(ColumnName)],
count(*) as [Count]
from [DatabaseName].[SchemaName].[TableName]
group by len(ColumnName)
order by len(ColumnName)

Upvotes: -1

Goutham
Goutham

Reputation: 41

Select count(*), Length(column_name) from table_name group by Length(column_name);

This will work for the different lengths in a single column.

Upvotes: 4

Chris
Chris

Reputation: 23171

select distinct(LENGTH(lastname)) from mynames;

Upvotes: 7

Joel Coehoorn
Joel Coehoorn

Reputation: 415725

SELECT Length(lastname)
FROM MyTable
GROUP BY Length(lastname)

Upvotes: 12

Related Questions