Re: SQL REQ: counting data and multiple tables

Steve,

My goodness that is GREAT!!!!

I am constantly impressed by the knowledge level of people like you.

Many Thanks!!

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 - 10:49:15 CST