There are two important fields kernel and property in the table mydata:
I want to CREATE a new table myresults with columns rightTwoKernelSymbols, property and frequency based only on mydata table so, that it displays the frequency of right two letters among kernel column. (property values should be equivalent, otherwise count separately)
So, using another words:
Here is 'mydata' table. I modify 'kernel' column by leaving only two right symbols in each cell.
Using this table, I want to count distinct pairs (kernel, property) and save the data into myresults table.
Example:
mydata Table
czzzaa - 123
abc80 - 123
aaaaaaaa - 123
zz5 - 123
abc80 - 456
modified Table
aa - 123
80 - 123
aa - 123
z5 - 123
80 - 456
myresults Table
aa - 123 - 2 // czzzaa and aaaaaaaa - total two times (123 is the same)
80 - 123 - 1
z5 - 123 - 1
80 - 456 - 1 //we don't count abc80 and abc80 together, because 123 is different from 456
OK, here is my answer on my own question. Please let me know if there are any pitfalls that weren't mentioned.
CREATE TABLE results AS SELECT right(kernel, 2) as two , property, COUNT(*) as count FROM mydata GROUP BY two, property ORDER BY count DESC;