Re: SQL REQ: counting data and multiple tables

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 - 07:35:12 CST