Re: COUNT Function

From: The Magnet <art_at_unsu.com>
Date: Mon, 17 May 2010 08:32:41 -0700 (PDT)
Message-ID: <e570f7ab-45c8-4a69-a7be-7328b473333a_at_40g2000vbr.googlegroups.com>

On May 17, 10:30 am, Carlos <miotromailcar..._at_netscape.net> wrote:
> 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:
>
> CAR..._at_XE.bequeath> select * from tags;
>
>     TAG_ID TAG_NAME
> ---------- ----------
>          1 TAG 1
>          2 TAG 2
>          3 TAG 3
>
> CAR..._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
>
> CAR..._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.

Ok, maybe using all those analytical functions was not necessary here. although I love them.

Thanks. Received on Mon May 17 2010 - 10:32:41 CDT