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
