This query works perfectly and responds much faster than my (SELECT COUNT(*)… approach.
Thanks a lot for all your effort and help! -steffen Pavel Ivanov-2 wrote: > > Sorry, forgot END after each case, so it should look like > > SELECT > tag_name.id, > SUM(CASE WHEN tag_link.app = 't' THEN 1 ELSE 0 END) AS cntTwt, > SUM(CASE WHEN tag_link.app = 'd' THEN 1 ELSE 0 END) AS cntDel > FROM > tag_name, tag_link > WHERE > tag_link.tag_id = tag_name.id > GROUP BY > tag_name.id > > > Pavel > > On Sun, May 10, 2009 at 7:40 PM, Pavel Ivanov <paiva...@gmail.com> wrote: >> Try something like this: >> >> SELECT >> tag_name.id, >> SUM(CASE WHEN tag_link.app = 't' THEN 1 ELSE 0) AS cntTwt, >> SUM(CASE WHEN tag_link.app = 'd' THEN 1 ELSE 0) AS cntDel >> FROM >> tag_name, tag_link >> WHERE >> tag_link.tag_id = tag_name.id >> GROUP BY >> tag_name.id >> >> >> Pavel >> >> On Sun, May 10, 2009 at 6:09 PM, 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. >>> >>> 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 >>> >> > _______________________________________________ > 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-tp23473911p23480948.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
