Re: COUNT Function
From: Carlos <miotromailcarlos_at_netscape.net>
Date: Mon, 17 May 2010 08:30:10 -0700 (PDT)
Message-ID: <f67dfb15-88e5-4245-8701-ff8a6a5b1a14_at_w3g2000vbd.googlegroups.com>
On May 17, 5:01 pm, The Magnet <a..._at_unsu.com> wrote:
> Hi,
>
> I'm tryng to use the Analytical function COUNT(*):
>
> SELECT tag_id, tag_name, tag_count
> FROM (SELECT t.tag_id, tag_name,
> COUNT(*) OVER (PARTITION BY t.tag_id) tag_count,
> ROW_NUMBER() OVER (PARTITION BY t.tag_id ORDER BY
> t.tag_id) rnum
> FROM commentary.article_tags a, commentary.tags t
> WHERE t.tag_id = a.tag_id(+))
> WHERE rnum = 1;
>
> Problem I am having is that for records in the TAGS table which do not
> match in the ARTICLE_TAGS table are still being returned with 1 row.
> I'm looking for it to return 0, as there were no matches.
>
> Thought it was the way the join was working, but I do not think so as
> I've tried different combos.
>
> Any ideas?
6 WHERE t.tag_id = a.tag_id(+)
7 group by t.tag_id, t.tag_name;
Date: Mon, 17 May 2010 08:30:10 -0700 (PDT)
Message-ID: <f67dfb15-88e5-4245-8701-ff8a6a5b1a14_at_w3g2000vbd.googlegroups.com>
On May 17, 5:01 pm, The Magnet <a..._at_unsu.com> wrote:
> Hi,
>
> I'm tryng to use the Analytical function COUNT(*):
>
> SELECT tag_id, tag_name, tag_count
> FROM (SELECT t.tag_id, tag_name,
> COUNT(*) OVER (PARTITION BY t.tag_id) tag_count,
> ROW_NUMBER() OVER (PARTITION BY t.tag_id ORDER BY
> t.tag_id) rnum
> FROM commentary.article_tags a, commentary.tags t
> WHERE t.tag_id = a.tag_id(+))
> WHERE rnum = 1;
>
> Problem I am having is that for records in the TAGS table which do not
> match in the ARTICLE_TAGS table are still being returned with 1 row.
> I'm looking for it to return 0, as there were no matches.
>
> Thought it was the way the join was working, but I do not think so as
> I've tried different combos.
>
> Any ideas?
I think i don't get it.
Why not:
CARLOS_at_XE.bequeath> select * from tags;
TAG_ID TAG_NAME
---------- ----------
1 TAG 1 2 TAG 2 3 TAG 3
CARLOS_at_XE.bequeath> select * from article_tags;
ARTICLE_ID ARTICLE_NAME TAG_ID
---------- ------------ ----------
1 ARTICLE 11 1 1 ARTICLE 11 1 1 ARTICLE 12 1 2 ARTICLE 21 2 CARLOS_at_XE.bequeath> SELECT t.tag_id, 2 t.tag_name, 3 COUNT(a.tag_id) tag_count 4 FROM article_tags a, 5 tags t
6 WHERE t.tag_id = a.tag_id(+)
7 group by t.tag_id, t.tag_name;
TAG_ID TAG_NAME TAG_COUNT
---------- ---------- ----------
3 TAG 3 0 1 TAG 1 3 2 TAG 2 1
HTH. Cheers.
Carlos. Received on Mon May 17 2010 - 10:30:10 CDT