CODEWITHSUNDEEP
sqlsqlite
Tunde Awosanya
Tunde Awosanya

Reputation: 365

Counting occurrences of features in sqlite

I have a data set of word sets and their frequencies, e.g.

w1  w2  w3   freq
a   a   a    4
a   a   and  3
a   a   band 1
a   a   well 1
a   and a    2

I want to get the counts of the observations according to the table below:

            (w3)   not(w3)
(w1,w2)      n1     n2
not(w1,w2)   n3     n4

where n1,...,n4 are sums of the frequencies of the observations that satisfies the conditions. For example, in the first observation, w1=a , w2=a , w3=a. We will now check all the observations where w1=a , w2=a , w3=a. We find only that one observation meets that criteria and its frequency is 4. Next we do w1=a , w2=a , w3!=a and that gives us the observations with frequencies being 3,1,1 and the sum is 5. Now we will do w1!=a , w2!=a , w3=a which is 0 and w1!=a , w2!=a , w3!=a is 0.

I want a table that will be output as:

w1  w2  w3   freq  n1  n2  n3  n4
a   a   a    4     4   5   0   0
a   a   and  3     3   6   0   0
a   a   band 1
a   a   well 1
a   and a    2
etc.

How can I achieve this using sqlite3?

Upvotes: 1

Views: 49

Answers (1)

CL.
CL.

Reputation: 180300

This can be done with correlated, scalar subqueries:

SELECT w1,
       w2,
       w3,
       freq,
       (SELECT SUM(freq)
        FROM MyLittleTable AS T2
        WHERE T2.w1 = T1.w1
          AND T2.w2 = T1.w2
          AND T2.w3 = T1.w3
       ) AS n1,
       (SELECT SUM(freq)
        FROM MyLittleTable AS T2
        WHERE T2.w1  = T1.w1
          AND T2.w2  = T1.w2
          AND T2.w3 != T1.w3
       ) AS n2,
       ...
FROM MyLittleTable AS T1

Upvotes: 1

Related Questions