Prev: Recommendation needed on Oracle Security Training
Next: Developing Query
From: Michel Cadot on 17 May 2010 11:37

"The Magnet" <art(a)unsu.com> a �crit dans le message de news: cc1222a8-dd9e-440d-ab5c-c0d16795ec69(a)m33g2000vbi.googlegroups.com...
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.

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


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


From: Carlos on 18 May 2010 06:00
>
> Ok, maybe using all those analytical functions was not necessary
> here.  although I love them.
>
> Thanks.

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

Cheers.

Carlos.
First  |  Prev  | 
Pages: 1 2
Prev: Recommendation needed on Oracle Security Training
Next: Developing Query