Re: COUNT Function

"The Magnet" <art_at_unsu.com> a écrit dans le message de news: d34c6b28-c9b1-4bb8-b973-94aece1cc1ee_at_y21g2000vba.googlegroups.com...
| 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?

Do not count after the outer join because you will have of course at least one row, count inside the outer joined table and nvl to 0.

Regards
Michel Received on Mon May 17 2010 - 10:09:37 CDT