Hi Kees, thanks for your help. Thats a neater way than I structured my JOIN version before. But my goal is to have all tag COUNTs for each application in one result row + id and name of the tag. Like:
tag-id | tag-name | count_app_t | count_app_d ----------------------------------------------------- 1 | sql | 9 | 2 2 | xml | 61 | 0 3 | foo | 47 | 826 Until now no 'JOIN construction' allowed more than one COUNT. Thats why I tried out the SELECT (SELECT …), (SELECT) way – which didn't though errors, but produced the freeze of the script. Regards, -steffen Kees Nuyt wrote: > > On Sun, 10 May 2009 13:51:29 -0700 (PDT), ckeen > <ste.fied...@googlemail.com> wrote: > >> >>Hi, I'm trying to count two different column combinations using two tables. >>Table one (tag_name) contains the 'id' and the 'name' for a tag. Table two >>is stuffed with entries that links those tags with different applications >>and their contents. >>Now I would like to select how often each tag is used in each application. >>Some of my previous tries worked fine (using JOIN ON) – but only one COUNT >>was possible. Now I'm trying to get a statement to work which returns me the >>tag amounts for both apps. >> >>tag_name >>id | tag >>-------- >>1 | sql >>2 | xml >>3 | foo >> >>tag_link >>id | app | app_id | tag_id >>---------------------------- >>1 | d | 331 | 2 >>2 | t | 49 | 1 >> >>Here is my current statement: >>SELECT >> tag_name.id, >> (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND >>tag_link.app = 't') AS cntTwt, >> (SELECT COUNT(*) FROM tag_link WHERE tag_link.tag_id = tag_name.id AND >>tag_link.app = 'd') AS cntDel >>FROM >> tag_name >>GROUP BY >> tag_name.id > > > sqlite_version():3.6.13 > CREATE TABLE tag_name ( > id INTEGER PRIMARY KEY, > tag TEXT > ); > CREATE TABLE app_name ( > id INTEGER PRIMARY KEY, > app TEXT > ); > CREATE TABLE tag_link ( > app_id INTEGER, > tag_id INTEGER, > PRIMARY KEY (app_id,tag_id) > ); > INSERT INTO tag_name values (1,'sql'); > INSERT INTO tag_name values (2,'xml'); > INSERT INTO tag_name values (3,'foo'); > > INSERT INTO app_name values (30,'a'); > INSERT INTO app_name values (39,'b'); > INSERT INTO app_name values (49,'t'); > INSERT INTO app_name values (331,'d'); > > INSERT INTO tag_link values (331,1); > INSERT INTO tag_link values (331,2); > INSERT INTO tag_link values (49,1); > INSERT INTO tag_link values (30,1); > INSERT INTO tag_link values (39,2); > INSERT INTO tag_link values (331,3); > INSERT INTO tag_link values (49,3); > > SELECT app_name.id,app,COUNT(tag_link.tag_id) AS nrtags > FROM tag_link > INNER JOIN app_name ON (app_name.id = tag_link.app_id) > GROUP BY tag_link.app_id; > > app_name.id|app_name.app|nrtags > 30|a|1 > 39|b|1 > 49|t|2 > 331|d|3 > > >>The parser returns no error, only seems to freeze. Tipps, hints – all kind >>of advice. >>sincerely, ckeen > > Hope this helps. > -- > ( Kees Nuyt > ) > c[_] > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Multiple-counts-between-two-tables-tp23473911p23474562.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
