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
