Re: [sqlite] Multiple counts between two tables

2009-05-11 Thread S Fiedler 

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 = '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  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 
>> 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
  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
>>>
>>
> ___

Re: [sqlite] Multiple counts between two tables

2009-05-10 Thread Pavel Ivanov 
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  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  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
>>>  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

Re: [sqlite] Multiple counts between two tables

2009-05-10 Thread Pavel Ivanov 
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  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
>>  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

Re: [sqlite] Multiple counts between two tables

2009-05-10 Thread S Fiedler 

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 the
tag occurred.
However, after the 
http://www.nabble.com/forum/ViewPost.jtp?post=23474516&framed=y hint  from P
Kishor I waited about four minutes and the freeze was gone. My query seems
to work but it takes much to long. Currently the table 'tag_name' has 6690
and 'tag_link' 50714 entries.
Your JOIN-query felt like one second – but only with one COUNT :/


Kees Nuyt wrote:
> 
> 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:
>>
>>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
>>>  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-tp23473911p23474904.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

Re: [sqlite] Multiple counts between two tables

2009-05-10 Thread Kees Nuyt 
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:
>
>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
>>  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

Re: [sqlite] Multiple counts between two tables

2009-05-10 Thread S Fiedler 

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
>  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

Re: [sqlite] Multiple counts between two tables

2009-05-10 Thread P Kishor 
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 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
>
> The parser returns no error, only seems to freeze. Tipps, hints – all kind
> of advice.
> sincerely, ckeen


Works for me

[08:56 PM] ~/Projects/postgis-1.3.6$sqlite3
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table tag_name (id, tag);
sqlite> create table tag_link (id, app, app_id, tag_id);
sqlite> insert into tag_name values (1, 'sql');
sqlite> insert into tag_name values (2, 'xml');
sqlite> insert into tag_name values (3, 'foo');
sqlite> insert into tag_link values (1, 'd', 331, 2);
sqlite> insert into tag_link values (2, 't', 49, 1);
sqlite> SELECT tag_name.id,
   ...> (SELECT COUNT(*) FROM tag_link a JOIN tag_name b ON a.id =
b.id AND a.app = 't') AS cntTwt,
   ...> (SELECT COUNT(*) FROM tag_link a JOIN tag_name b ON a.id =
b.id AND a.app = 'd') AS cntDel
   ...> FROM tag_name
   ...> GROUP BY tag_name.id;
id  cntTwt  cntDel
--  --  --
1   1   1
2   1   1
3   1   1
sqlite>




-- 
Puneet Kishor http://www.punkish.org/
Carbon Model http://carbonmodel.org/
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/
Science Commons Fellow, Geospatial Data http://sciencecommons.org
Nelson Institute, UW-Madison http://www.nelson.wisc.edu/
---
collaborate, communicate, compete
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple counts between two tables

2009-05-10 Thread Kees Nuyt 
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 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