Find the count of the first character of `Name` across all values then sort it ( PostgreSQL )
postgresql select substring first character
postgres string starts with
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
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;
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 FROM Tbl GROUP BY LEFT(Name,1) 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
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;
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: https://rextester.com/MTRRK31478
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 BYquery 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