Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL REQ: counting data and multiple tables

Re: SQL REQ: counting data and multiple tables

From: Alex Skolnic <ak_at_e-brag.com>
Date: Thu, 29 Mar 2001 22:20:37 GMT
Message-ID: <vvc7ctoc9k2uotqb754j69k6kjd97i8iev@4ax.com>

Steve,

That is EXACTLY what I was looking for.

I can't thank you enough!

the check is in the mail! :-)

Thanks again,

Alex

On Thu, 29 Mar 2001 20:21:17 GMT, Steve Bell <swayne.bell_at_sympatico.ca> wrote:

>Hi Alex,
>If I understand you correctly, you could do that by using an outer join in
>the where clause. So, you could rewrite the same script I suggested earlier
>like this:
>
>create or replace view deptemp as
> select d.deptno,d.dname,count(e.empno) EMPLOYEES
> from emp e, dept d
> where e.deptno (+) = d.deptno -- outer join (includes dept even if no emp)
> group by d.deptno,d.dname
>
>So, then your view would show empty departments, like this:
>SQL> select * from deptemp;
>
> DEPTNO DNAME EMPLOYEES
>---------- -------------- ----------
> 10 ACCOUNTING 11
> 20 RESEARCH 22
> 30 SALES 31
> 40 OPERATIONS 0
> 60 FINANCE 13
> 62 SECURITY 0
> 70 PLANNING 18
> 80 PURCHASING 17
> 90 SHIPPING 0
> 91 EMPTY_DEPT 0
>
>10 rows selected.
>
>Is this what you want?
>
>Steve
>
>Alex Skolnic wrote:
>
>> Steve,
>>
>> Would it be possible to return a 0 count for a DepNo?
>>
>> In other words, would it be possible to return ALL the departments all
>> the time with the count regardless if an entry exists in the employees
>> table?
>>
>> On Thu, 29 Mar 2001 13:35:12 GMT, Steve Bell
>> <swayne.bell_at_sympatico.ca> wrote:
>>
>> >Hi Alex,
>> >
>> >I posted a reply to this when you were asking how to do it with a view..
>> >
>> >Here's an example syntax:
>> >
>> >Hi,
>> >This is one of the main benefits of a view.
>> >Here's a sample using DEPT and EMP tables..just adapt it to
>> >your tables. You'll need an alias for the count( ) column...
>> >
>> > create view deptemp as
>> > select d.deptno,d.dname,count(e.empno) EMPLOYEES
>> > from emp e, dept d
>> > where e.deptno = d.deptno
>> > group by d.deptno,d.dname
>> >
>> >SQL> SELECT * FROM DEPTEMP
>> > 2 ;
>> >
>> > DEPTNO DNAME EMPLOYEES --- this is the count
>> >---------- -------------- ----------
>> > 10 ACCOUNTING 11
>> > 20 RESEARCH 22
>> > 30 SALES 31
>> > 60 FINANCE 13
>> > 70 PLANNING 18
>> > 80 PURCHASING 17
>> >
>> >6 rows selected.
>> >
>> >Alex Skolnic wrote:
>> >
>> >> HI All,
>> >>
>> >> What I would to accomplish is a way that I can query my accounts table
>> >> and then use the emp_id to determine how many enteries that emp_id has
>> >> in another table. I am currently using multiple SQL statements:
>> >>
>> >> For the Accounts table:
>> >> Select emp_id, Name from Accounts
>> >>
>> >> To find the number of instances
>> >> select count(emp_id) from purchase where empid=(emp_id to look for);
>> >>
>> >> is it possible to do this type of query with ONE SQL statement (
>> >> Possibly a view/Stored Proc) and have it return:
>> >>
>> >> Emp_id Name Count (purchase table)
>> >> 11111 abc 20
>> >> 22222 def 1
>> >> 33333 ghi 20
>> >>
>> >> Is this even possible? If not, what would be the best way to
>> >> accomplish this with a single select statement?
>> >>
>> >> Any and all help would be greatly appreciated.
Received on Thu Mar 29 2001 - 16:20:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US