On Sun, 10 May 2009 15:09:01 -0700 (PDT), 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.
Aha, I see, you mean a pivot report.
That can't be easily done in plain SQL.
What is weird in your example, is that the same application
('d') uses the same tag more then once. Is that on purpose?
In other words, it's not completely clear to me what you are
trying to accomplish.
>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
