[sqlite] GROUP BY with self join

On 2/14/2015 6:55 PM, R.Smith wrote: > > On 2/14/2015 6:32 PM, Bart Smissaert wrote: >> Having problems with the following SQL: >> >> 2 tables, trying to count occurrence of field 2 and field 3 of table 1 >> in field 1 (only field) of table 2. ... > I haven't tested it, but my quick-scan answer w

[sqlite] GROUP BY with self join

On 2/14/2015 6:32 PM, Bart Smissaert wrote: > Having problems with the following SQL: > > 2 tables, trying to count occurrence of field 2 and field 3 of table 1 > in field 1 (only field) of table 2. > > Table 1 called ITEM with fields: NAME, DEFINITION1, DEFINITION2 > all text fields. Values in NA

[sqlite] test for bounce detection

On 2/14/2015 10:18 AM, Ma?l Nison wrote: > As a side, it's the first time that I received a mail from this mailing > list without it being automatically flagged as spam. Well, technically this is spam... but it's the good kind :) Thank you kindly Mike for the prompt repair and keeping things runn

[sqlite] GROUP BY with self join

Looked in the documentation and the answer seems to use total instead of sum. This is for the ratio field, not the 2 count fields. Looks all sorted now and thanks again. RBS On Sat, Feb 14, 2015 at 5:13 PM, Bart Smissaert wrote: > One more thing. How would I get the ratio of the 2 counts, so co

[sqlite] GROUP BY with self join

One more thing. How would I get the ratio of the 2 counts, so count1 / (count1 + count2) ? select NAME, sum(INSTR(FULL_TEXT, DEFINITION1) > 0) as count1, sum(INSTR(FULL_TEXT, DEFINITION2) > 0) as count2, count1 / (count1 + count2) as ratio from Items join Descriptions group by NAME is not

[sqlite] GROUP BY with self join

Thanks, that works nicely indeed. I tried the first SQL, will try second as well. I wasn't aware of the construction with join without the fields to join on after the join keyword. It looks strange to me. I take it the joining fields are done in the select. RBS On Sat, Feb 14, 2015 at 4:49 PM, I

[sqlite] GROUP BY with self join

Having problems with the following SQL: 2 tables, trying to count occurrence of field 2 and field 3 of table 1 in field 1 (only field) of table 2. Table 1 called ITEM with fields: NAME, DEFINITION1, DEFINITION2 all text fields. Values in NAME are all unique. Table 2 called DESCRIPTIONS with only

[sqlite] GROUP BY with self join

On 2/14/2015 11:32 AM, Bart Smissaert wrote: > SELECT I.ITEM_NAME, COUNT(D.ROWID), COUNT(D2.ROWID) FROM ITEMS I > INNER JOIN DESCRIPTIONS D ON (INSTR(D.FULL_TEXT, I.DEFINITION1) > 0) > INNER JOIN DESCRIPTIONS D2 ON (INSTR(D2.FULL_TEXT, I.DEFINITION2) > 0) > GROUP BY I.NAME You are doing a cross pr

[sqlite] test for bounce detection

As a side, it's the first time that I received a mail from this mailing list without it being automatically flagged as spam. Le sam. 14 f?vr. 2015 06:26, Simon Slavin a ?crit : > On 14 Feb 2015, at 4:25am, Mike Owens wrote: > > > So I updated mailman today to use mailinglists.sqlite.org rather

[sqlite] test for bounce detection

GMail has constantly and randomly flagged different messages from this mailing list as spam. I set a specific mail rule to force the mail to NOT be flagged as spam. The nice thing is, GMail also tells me when the message SHOULD have been put into spam but because of my mail rule, the message ende

[sqlite] test for bounce detection

On 14 Feb 2015, at 4:25am, Mike Owens wrote: > So I updated mailman today to use mailinglists.sqlite.org rather than > sqlite.org:8080 knowing that there is a good chance some issues will arise. > I have been fixing them as they surface. This issue is related to the > Postfix config not recognizi

[sqlite] test for bounce detection

I can confirm the bounces happen for the ".mailinglists" email address which is now automatically added to the "Reply-To" address. I hit the reply-to-all button and then remove the .mailinglists address to fix it, but it is rather cumbersome, if the powers that control such things could kindly

[sqlite] Fwd: Re: sqlite journal file question

On 2/14/2015 12:19 AM, Mayank Kumar (mayankum) wrote: > Thanks all for the responses. Just want to clarify the scenario one more > time:- > > -by syncing, I mean taking the modifications on the active machine and > sending over wire to another machine(in some proprietary format) , where > ther

[sqlite] test for bounce detection

On 13 Feb 2015, at 11:39pm, David King wrote: > Maybe unrelated, but in the last few hours the list ID header changed from > "sqlite-users.sqlite.org" to "sqlite-users.mailinglists.sqlite.org", which > messes up my mail filtering. Thanks, David. That was it. For others: messages from the

[sqlite] sqlite journal file question

On 13 Feb 2015, at 10:19pm, Mayank Kumar (mayankum) wrote: > -by syncing, I mean taking the modifications on the active machine and > sending over wire to another machine(in some proprietary format) , where > there is a similar sqlite application which receives the records and the > records