Re: [sqlite] Multiple counts between two tables

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 =

Re: [sqlite] Multiple counts between two tables

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

Re: [sqlite] Multiple counts between two tables

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

Re: [sqlite] Multiple counts between two tables

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

Re: [sqlite] Multiple counts between two tables

On Sun, 10 May 2009 15:09:01 -0700 (PDT), S Fiedler 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:

Re: [sqlite] Multiple counts between two tables

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

Re: [sqlite] Multiple counts between two tables

On Sun, May 10, 2009 at 3:51 PM, ckeen 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

Re: [sqlite] Multiple counts between two tables

On Sun, 10 May 2009 13:51:29 -0700 (PDT), ckeen 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

[sqlite] Multiple counts between two tables

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