Existing table. In the range of lines with the same field "cc" count the number of codes - AU =, US =, etc. table

  • Do you need to count unique words, or all? - vp_arth
  • @vp_arth In a group of lines with the same "ss", quantities of all kinds of words are needed. Let's say in two lines with “сс = BR” AU = 2, BR = 1, CA = 2, CN = 2, EP = 2, KR = 1, MX = 1, US = 2, WO = 2. - Pavels Papsh
  • Great, read about normalization. In this form, it will be difficult to do. - vp_arth

1 answer 1

As I know, there is no way to directly calculate the number of tokens by separator in sqlite .
However, you can use the functions Length and Replace :

 SELECT cc, length(listOfCountries) - length(replace(listOfCountries, ' ', '')) + 1 FROM table;

To find unique values ​​for group by cc most likely have to normalize the table.

  • Normalize to what form? - Pavels Papsh
  • Before the first, at least. You need to have a table with a list of all countries, and a table of relations cc <-> страна . The so-called many-to-many relationship. You cannot store a serialized list of entities if you plan to carry out operations (counting, grouping) with them separately - vp_arth
  • I was afraid of normalization. Excel table, which is shown, was not formed by me. I imported it into SQLite as is, with lots of redundancy. That is, I need to create the right structure with normal links and references. Then from the primary table to transfer records to a new one. Tell me, is it possible to get out of the situation? If yes, I would like a small example. - Pavels Papsh