COUNT Function
in [Oracle Help]
|
From: The Magnet on 17 May 2010 11:01 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?
From: Michel Cadot on 17 May 2010 11:09 "The Magnet" <art(a)unsu.com> a �crit dans le message de news: d34c6b28-c9b1-4bb8-b973-94aece1cc1ee(a)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
From: The Magnet on 17 May 2010 11:19 On May 17, 10:09 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote: > "The Magnet" <a...(a)unsu.com> a écrit dans le message de news: d34c6b28-c9b1-4bb8-b973-94aece1cc...(a)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 Not sure what you mean "after" the outer join. I thought I was already counting within.
From: Carlos on 17 May 2010 11:30 On May 17, 5:01 pm, The Magnet <a...(a)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(a)XE.bequeath> select * from tags; TAG_ID TAG_NAME ---------- ---------- 1 TAG 1 2 TAG 2 3 TAG 3 CARLOS(a)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(a)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.
From: The Magnet on 17 May 2010 11:32 On May 17, 10:30 am, Carlos <miotromailcar...(a)netscape.net> wrote: > On May 17, 5:01 pm, The Magnet <a...(a)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...(a)XE.bequeath> select * from tags; > > TAG_ID TAG_NAME > ---------- ---------- > 1 TAG 1 > 2 TAG 2 > 3 TAG 3 > > CAR...(a)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...(a)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.
|
Next
|
Last
Pages: 1 2 Prev: Recommendation needed on Oracle Security Training Next: Developing Query |