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

postgres find second occurrence in string
postgresql select substring first character
postgres string starts with
postgres regex
postgresql substring
postgres substring regex
postgresql substring between two characters

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.

Try this option:

SELECT LEFT(Name, 1) AS letter, COUNT(*) AS cnt
FROM your_table


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.

Documentation: 9.3: String Functions and Operators, Strings in this context include values of the types character, character varying, and text. Unless otherwise noted, all of the functions listed below work on all of these initcap(string), text, Convert the first letter of each word to upper case and the length(string bytea, encoding name ), int, Number of characters in string in the  COUNT (*) The COUNT (*) function returns the number of rows returned by a SELECT statement, including NULL and duplicates. SELECT COUNT (*) FROM table_name WHERE condition; When you apply the COUNT (*) function to the entire table, PostgreSQL has to scan the whole table sequentially.

Try like this

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

PostgreSQL ORDER BY, Second, use ASC to sort the result set in ascending order and DESC to sort the the ORDER BY clause to sort customers by the first name in ascending order:. 9.4. String Functions and Operators. This section describes functions and operators for examining and manipulating string values. Strings in this context include values of the types character, character varying, and text.

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

with first_letters as
    upper(left(Name,1)) as letter_uppercase,
    count(*) as count_ignore
    from table1
    group by upper(left(Name,1))
occurrences as (
    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))
sum(result.occurrence) as sum_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,

PostgreSQL LENGTH() function, The PostgreSQL length() function is used to find the length of a string i.e. and the length of first_name ( how many characters contain in the first name ) from the employees where the length of first_name is more than 7. Code  Count substrings with same first and last characters; Maximum consecutive repeating character in string; Count of strings that can be formed using a, b and c under given constraints; Group words with same set of characters; Count of total anagram substrings; Substring with highest frequency length product; Number of even substrings in a string of digits

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:

Retrieving Rows with SELECT, The source, in this case, may be a table name or a sub-query. For example, a LIMIT with a count set to 100, and an OFFSET clause with a start value of 50 would A SELECT statement may be as simple as a request for all rows and all If specified, PostgreSQL will be unable to determine if the id column refers to the  Finding index positions of single or multiple characters in a string Count Occurrences and find all index position of a single character in a String. To find the index positions of a given character in string using regex, create a regex pattern to match the character. Then iterate over all the matches of that pattern in the string and add their

Using SQL String Functions to Clean Data, This lesson of the SQL tutorial for data analysis covers SQL string functions to LEFT , RIGHT , and TRIM are all used to select only certain elements of POSITION allows you to specify a substring, then returns a numerical value equal to the character number (counting from left) where that substring first appears in the  1. Select all the cells you want to count the first instance, and then click Kutools > Select > Select Duplicate & Unique Cells. See screenshot: 2. In the Select Duplicate & Unique Cells dialog, check All unique (Including 1st duplicates) option in the Rule section, and then you can choose whether fill the selected cells with background or font

REGEXP_COUNT function - Amazon Redshift, Searches a string for a regular expression pattern and returns an integer that indicates If no match is found, then the function returns 0. If position is less than 1, the search begins at the first character of source_string. The following example counts the number of times the top-level domain name is All rights reserved. The COUNT subparameter of TRAILER1 provides the number of records for the entire report (all the records) starting at position 1. REMOVECC omits the ANSI carriage control character from all of the report records. NODETAIL generates a report with no data records, only header and trailer records, as specified. Hope this helps.

MySQL 8.0 Reference Manual :: 12.20.1 Aggregate , The SUM() and AVG() functions return a DECIMAL value for exact-value (They convert the values to numbers, losing everything after the first nonnumeric character.) Returns a count of the number of non- NULL values of expr in the rows the DESC (descending) keyword to the name of the column you are sorting by in  Posted: Mon Feb 23, 2009 10:49 pm Post subject: SORT - Count of records (grouped by a field) Hi, I need to group the records by a particular field, and get the no. of records per group.

  • This seems like a pretty basic GROUP BY query with a string function. What have you tried?
  • I tried using left(name,1) but not able to figure out how should I iterate in rows to know the count.
  • A is repeated three times in the table , 1 "a" in "Jaine" and 2 "a" in Arya @a_horse_with_no_name
  • From your output A=3,J=2,M=1. Could you illustrate how the expected count of A is 3
  • Yes you are correct @a_horse_with_no_name. It is a non-reserved keyword and quotes are not required. Answer updated. As documented in the Postgresql Documentation