On Sun, May 10, 2009 at 3:51 PM, 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 > > The parser returns no error, only seems to freeze. Tipps, hints – all kind > of advice. > sincerely, ckeen
Works for me [08:56 PM] ~/Projects/postgis-1.3.6$sqlite3 SQLite version 3.6.11 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table tag_name (id, tag); sqlite> create table tag_link (id, app, app_id, tag_id); sqlite> insert into tag_name values (1, 'sql'); sqlite> insert into tag_name values (2, 'xml'); sqlite> insert into tag_name values (3, 'foo'); sqlite> insert into tag_link values (1, 'd', 331, 2); sqlite> insert into tag_link values (2, 't', 49, 1); sqlite> SELECT tag_name.id, ...> (SELECT COUNT(*) FROM tag_link a JOIN tag_name b ON a.id = b.id AND a.app = 't') AS cntTwt, ...> (SELECT COUNT(*) FROM tag_link a JOIN tag_name b ON a.id = b.id AND a.app = 'd') AS cntDel ...> FROM tag_name ...> GROUP BY tag_name.id; id cntTwt cntDel ---------- ---------- ---------- 1 1 1 2 1 1 3 1 1 sqlite> -- Puneet Kishor http://www.punkish.org/ Carbon Model http://carbonmodel.org/ Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/ Science Commons Fellow, Geospatial Data http://sciencecommons.org Nelson Institute, UW-Madison http://www.nelson.wisc.edu/ ----------------------------------------------------------------------- collaborate, communicate, compete ======================================================================= Sent from Madison, WI, United States _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
