CODEWITHSUNDEEP
sqlpostgresql
Mighty
Mighty

Reputation: 447

Find the count of the first character of `Name` across all values then sort it ( PostgreSQL )

I have a table with the following rows:

key Name
 1   Mighty
 2   Jon
 3   Jaine
 4   Arya

Now I want to count each non case-sensitive occurrence of the first character of all NAMEs in the table, then sort the result in descending order. If there is a tie (counts are equal for a multiple characters), it should sort the result in alphabetical order, so my output looks like:

A 3
J 2
M 1

A exists 3 times, once in Jaine and twice in Arya.

Upvotes: 0

Views: 621

Answers (4)

bruceskyaus
bruceskyaus

Reputation: 783

This code produces the expected result. Tested in SQL Fiddle. Data is assumed to be stored in table table1:

with first_letters as
(
    select
    upper(left(Name,1)) as letter_uppercase,
    count(*) as count_ignore
    from table1
    group by upper(left(Name,1))
),
occurrences as (
    select 
    t1.Name,
    length(t1.Name) - length(replace(upper(t1.Name), fl.letter_uppercase,'')) as occurrence
    from table1 t1
    inner join first_letters fl on fl.letter_uppercase = upper(left(t1.Name,1))
)
select
result.letter_uppercase,
sum(result.occurrence) as sum_occurrence
from
(
    select
    fl.letter_uppercase,
    o.occurrence
    from first_letters fl
    inner join occurrences o on upper(o.Name) like '%' || fl.letter_uppercase || '%' 
) result
 group by result.letter_uppercase
 order by sum(result.occurrence) desc,
          result.letter_uppercase;

Upvotes: 1

user330315
user330315

Reputation:

You need to first create a list of all characters in the table, then a list of all first characters and then combine them both: 

with all_chars as (
   select c
   from names, unnest(string_to_array(lower(name), null)) as t(c)
), first_chars as (
   select distinct lower(left(name,1)) as c
   from names
)
select fc.c, 
       (select count(*) from all_chars ac where ac.c = fc.c) as cnt
from first_chars fc
order by cnt desc, fc.c;

Online example: https://rextester.com/MTRRK31478

Upvotes: 0

Sanal Sunny
Sanal Sunny

Reputation: 617

Try like this

SELECT LEFT(Name,1) AS letter, COUNT(*) As ct
FROM Tbl
GROUP BY LEFT(Name,1) 
ORDER BY ct DESC,letter

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521499

Try this option:

SELECT LEFT(Name, 1) AS letter, COUNT(*) AS cnt
FROM your_table
GROUP BY LEFT(Name, 1)
ORDER BY COUNT(*) DESC, letter;

Demo

Note that the ORDER BY clause first sorts descending by the count, then sorts by first letter, should two or more letters be tied with the same count.

Upvotes: 1

Related Questions