Thanks for that, very nice indeed! I wasn't aware of the using keyword and will need to look that up.
RBS On Fri, Jan 19, 2018 at 10:58 PM, David Raymond <david.raym...@tomtom.com> wrote: > Since they both have the same set of names, then something like the below. > Again, since both have all the names there won't be a divide by 0 error to > worry about for the percentage. > > select > name as names, Table1_Count, Table2_Count, 1.0 * Table1_Count / > Table2_Count as percentage > from > (select name, count(*) as Table1_Count > from table1 > group by name > ) as table1counts > inner join > (select name, count(*) as Table2_Count > from table2 > group by name > ) as table2counts > using (name) > order by names;--optional > > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Friday, January 19, 2018 5:41 PM > To: General Discussion of SQLite Database > Subject: [sqlite] SQL frequency of names in 2 tables > > Say we have 2 tables, each with a text column, holding non-unique names. > All names in table 1 are also in table 2 and vice-versa. > The frequency of the names are different for both tables and this is the > information > I need to get. > > So output should be like this: > > Names Table1_Count Table2_Count Percentage > ------------------------------------------------------------------------ > Name1 3 9 33.33 > Name2 1 10 0.1 > > I am sure I am overlooking something simple, but not seen it yet. > Thanks for any idea. > > > RBS > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
