Re: Oracle Left Outer Join and totals

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Mon, 22 May 2006 20:11:04 GMT
Message-ID: <sVocg.54023$ge7.33367@trnddc01>

bbcrock_at_gmail.com wrote:
> I solved the issue. Another classic, "Once I posted it to the
> newsgroup, I was determined to solve it." Our DBA said it was not
> possible, because of the null rows, but of course it was. This code
> works (pseudocode, fyi, so no DDL, just DML and also, Oracle 9i):
>
> select Team.teamname, count(Report_ID)
> from Team LEFT OUTER JOIN Report
> ON Team.TeamID = Report.TeamID
> group by team.teamname
>
> Was hung up on the count(*)- that caused the bad results. running the
> count on report_id solved it.
>
> thanks!
>

SELECT t.teamname, NVL (v.cnt, 0) cnt
  FROM team t,

       (SELECT   teamid, COUNT (*) cnt
            FROM report
        GROUP BY teamid) v

 WHERE v.teamid(+) = t.teamid

Syntax may not be 100% right since I haven't tested it, but it should point you in the right direction. Received on Mon May 22 2006 - 15:11:04 CDT