Re: SQL REQ: counting data and multiple tables

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 - 11:14:54 CST