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
