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?

"The Magnet" <art (AT) unsu (DOT) com> a écrit dans le message de news: d34c6b28-c9b1-4bb8-b973-94aece1cc1ee...oglegroups.com...
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

On May 17, 10:09*am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

Not sure what you mean "after" the outer join. I thought I was
already counting within.

On May 17, 5:01*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:
I think i don't get it.

Why not:

CARLOS (AT) XE (DOT) bequeath> select * from tags;

TAG_ID TAG_NAME
---------- ----------
1 TAG 1
2 TAG 2
3 TAG 3

CARLOS (AT) XE (DOT) 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 (DOT) 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.

On May 17, 10:30*am, Carlos <miotromailcar... (AT) netscape (DOT) net> wrote:

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

Thanks.

"The Magnet" <art (AT) unsu (DOT) com> a écrit dans le message de news: cc1222a8-dd9e-440d-ab5c-c0d16795ec69...oglegroups.com...
On May 17, 10:09 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

Not sure what you mean "after" the outer join. I thought I was
already counting within.

------------------------------------------------


Not:
SQL> select deptno, dname, cnt
2 from ( select d.deptno, d.dname,
3 count(*) over (partition by d.deptno) cnt,
4 row_number () over (partition by d.deptno order by e.ename) rn
5 from dept d, emp e
6 where e.deptno (+) = d.deptno
7 )
8 where rn = 1
9 /
DEPTNO DNAME CNT
---------- -------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 1

4 rows selected.

But:

SQL> select d.deptno, d.dname, nvl(e.cnt,0) cnt
2 from dept d,
3 (select deptno, count(*) cnt from emp group by deptno) e
4 where e.deptno (+) = d.deptno
5 /
DEPTNO DNAME CNT
---------- -------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0

4 rows selected.

General note: using an analytic function and then restrict to 1 row
with "distinct" or "row_number" is a sign that you misuse the function
and you acutally want to use an aggregate (as I did).

Regards
Michel

I love my electric screwdriver, but I wouldn't use it to sink a nail
into the wall.

Cheers.

Carlos.