Oh – all the sql stuff has never been real easy for me at all. Basically I have a database which is filled by several applications working with tags. In the database there is only one table that contains the names of the tags – the other one links to the application and specifies in which content the tag occurred. However, after the http://www.nabble.com/forum/ViewPost.jtp?post=23474516&framed=y hint from P Kishor I waited about four minutes and the freeze was gone. My query seems to work but it takes much to long. Currently the table 'tag_name' has 6690 and 'tag_link' 50714 entries. Your JOIN-query felt like one second – but only with one COUNT :/
Kees Nuyt wrote: > > On Sun, 10 May 2009 15:09:01 -0700 (PDT), S Fiedler > <ste.fied...@googlemail.com> wrote: > >> >>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. > > Aha, I see, you mean a pivot report. > That can't be easily done in plain SQL. > > What is weird in your example, is that the same application > ('d') uses the same tag more then once. Is that on purpose? > In other words, it's not completely clear to me what you are > trying to accomplish. > >>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-tp23473911p23474904.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