• Welcome, Guest. Please login.
 
November 21, 2019, 03:05:50 pm

News:

Welcome to the SQLitening support forums!


Counting numer of lines from a DISTINCT-command

Started by Fim, August 13, 2014, 12:27:48 pm

Previous topic - Next topic

Fim

I have a table with many word lists, e.g astronauts, rocks, bridges Sweden, bridges World.
I create the table witH this:
CREATE TABLE Listord(
    Sprak       Text        not null,     = Language
    Listnamn    Text        not null,     = List name
    Ord         Text        not null,     = Word
    Unique (Sprak, Listnamn, Ord));

With this SQL-Command
SELECT DISTINCT  Listnamn FROM Listord where sprak = 'SE';
I can se the namne av all word lists, but, and that is the question,
how to count the number of word lists in the table??
                         
COUNT (SELECT DISTINCT  Listnamn FROM Listord where sprak = 'SE';)  No, No
SELECT COUNT (DISTINCT  Listnamn FROM Listord where sprak = 'SE';)  No, No     
/Fim W
Fim W

Pat Dooley

I am not sure what yo need.

maybe?

select Listnamm, count(*) as CNT from Listord group by Listnamm;


cj

Select count(DISTINCT Listnamn) FROM Listord where sprak = 'SE'


If the word index is unique then multiple lists cannot use the same word.
Creating an index of (listname,word) solves that problem.
If using DISTINCT the syntax is:  SELECT COUNT(DISTINCT col1), COUNT(DISTINCT col2)
To avoid conflicts when entering duplicates are not wanted  INSERT OR IGNORE is used.

Hope this helps

%Word_Allowed_In_Multiple_Lists = 1
%DropTable = 0
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG   'fin2.bas
  LOCAL sArray() AS STRING, sTitle AS STRING, sResult AS STRING
  IF %Word_Allowed_In_Multiple_Lists THEN
    sTitle = "Word allowed in multiple lists"
  ELSE
    sTitle = "Word not allowed in multiple lists"
  END IF
  slopen  "sample.db3","C"
  IF %DropTable THEN slExe "drop table if exists t1"
  IF %Word_Allowed_In_Multiple_Lists THEN
    slexe   "create table if not exists t1(id integer primary key, listname not null,word not null)"
  ELSE  'make word unique
    slexe   "create table if not exists t1(id integer primary key, listname not null,word not null UNIQUE)"
  END IF
slexe "Create unique index if not exists t1_index on t1(listname,word)"
  slexe   "insert or ignore into t1 values(null,'ListA','pear')"
  slexe   "insert or ignore into t1 values(null,'ListA','apple')"
  slexe   "insert or ignore into t1 values(null,'ListB','pear')"
  slexe   "insert or ignore into t1 values(null,'ListB','apple')"
  slSelAry "Select rowid as rows,count(DISTINCT listname) as lists, count(DISTINCT word) as words from t1",sArray(),"Q9"
  IF UBOUND(sArray) > -1 THEN
    sResult = JOIN$(sArray(),$CR)
  END IF
  slSelAry "Select * from t1 order by listname",sArray(),"Q9"
  IF UBOUND(sArray) > -1 THEN
    ? CHR$(sResult,$CR,$CR,JOIN$(sArray(),$CR)),,sTitle
  END IF
END FUNCTION

Results:
----------------------------------------------
Word allowed in multiple lists
Select rowid as rows,count(distinct listname) as lists, count(distinct word) as words from t1
rows  lists  words
4       2      2

Select * from t1 order by listname
id   listname   word
2   ListA         apple
1   ListA         pear
4   ListB         apple
3   ListB         pear

Fim

Hi,

Select count(DISTINCT Listnamn) FROM Listord where sprak = 'SE'
Exactly what I was looking for. A big thanks.
I have to learn SQL better.

Fim W.
Fim W

cj

I have these:
The SQL Guide to SQLIte by Rick F. van der Lans
The Definitive Guide to SQLite by Michael Owens (older first edition)

http://www.sqlite.org/books.html

Free ebook:
http://it-ebooks.info/book/147/

It you scan for distinct in this ebook it will describe the syntax and
mention that every row must be compared against every other row.
Use CTRL+F + F3 to find what you want quickly.
Please set the zoom, etc to fit your needs.
If you have multiple monitors it is fantastic.

Many documents on sqlite site:
Just one:
http://www.sqlite.org/lang_select.html  (many links on this page)


http://www.tutorialspoint.com/sqlite/index.htm

Of course just searching google with SQLITE + something may work.

I highly suggest writing a function executor with a couple of text boxes and an execute button.
SQL entered into one text box and the results appear in the other.
If you do this a single function can be created to execute all your SQL statements in all your programs.

SQLitening function executor with PBWIN 10 source code.
http://www.sqlitening.com/support/index.php?topic=9299.msg24468;topicseen#msg24468

Comments on the function executor are welcome.
It will execute unlimited lines as long as each line (even comment lines) are ended with a semicolon.




cj

Might want case insensitive list names and words by using collate nocase in the index statement.
This would prevent 'LISTA', 'ListA'  and multiple words like 'APPLE' and 'Apple".

Word_Allowed_In_Multiple_Lists = 1
%DropTable = 1
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG  'fin2.bas
  LOCAL sArray() AS STRING, sTitle AS STRING, sResult AS STRING
  IF %Word_Allowed_In_Multiple_Lists THEN
    sTitle = "Word allowed in multiple lists"
  ELSE
    sTitle = "Word not allowed in multiple lists"
  END IF
  slopen  "sample.db3","C"
  IF %DropTable THEN slExe "drop table if exists t1"
  IF %Word_Allowed_In_Multiple_Lists THEN
    slexe   "create table if not exists t1(id integer primary key, listname not null,word not null)"
  ELSE  'make word unique to all tables
    slexe   "create table if not exists t1(id integer primary key, listname not null,word not null UNIQUE collate nocase)"
  END IF
  slexe   "Create unique index if not exists t1_index on t1(listname collate nocase,word collate nocase)"
  slexe   "insert or ignore into t1 values(null,'ListA','pear')"
  slexe   "insert or ignore into t1 values(null,'ListA','apple')"
  slexe   "insert or ignore into t1 values(null,'ListB','pear')"
  slexe   "insert or ignore into t1 values(null,'ListB','apple')"
  slexe   "insert or ignore into t1 values(null,'ListB','APPLE')"
  slexe   "insert or ignore into t1 values(null,'Listb','APPLE')"
  slSelAry "Select rowid as rows,count(distinct listname) as lists, count(distinct word) as words from t1",sArray(),"Q9"
  IF UBOUND(sArray) > -1 THEN
    sResult = JOIN$(sArray(),$CR)
  END IF
  slSelAry "Select * from t1 order by listname,word",sArray(),"Q9"
  IF UBOUND(sArray) > -1 THEN
    ? CHR$(sResult,$CR,$CR,JOIN$(sArray(),$CR)),,sTitle
  END IF
END FUNCTION