CODEWITHSUNDEEP
sqloracle-database
Hussain
Hussain

Reputation: 11

want to use count function in sql by joining 3 tables

I want to answer this query:

"Print the total number of projects controlled by the different departments and the manager of the project."

I am using following query, 

select a.dname as "Departement", b.fname as "Manager", c.pname as "Project" , c.dnum
from "DEPARTMENT " a ,
     "EMPLOYEE " b ,
     "PROJECT " c
where a.dnumber = c.dnum
  and a.mgr_ssn = b.ssn ;

I just want to add count function to return me the number of projects controlled by each manager.

Upvotes: 0

Views: 134

Answers (3)

Gaurav Tyagi
Gaurav Tyagi

Reputation: 968

Here is your query 

   select a.dname as Department, b.fname as Manager, count(c.pname) as Project, c.dnum
     from DEPARTMENT  a 
     join EMPLOYEE    b on a.mgr_ssn = b.ssn 
     join PROJECT     c on a.dnumber = c.dnum 
     group by a.dname,b.fname,c.dnum

Upvotes: 0

Bryan Newman
Bryan Newman

Reputation: 631

The previous answer and this one both assume no two projects have the same name - is there a unique key on the project table?

select a.dname as Departement, 
       b.fname as Manager, 
       c.pname as Project, 
       c.dnum,
       count(distinct d.pname)
from DEPARTMENT a,
     EMPLOYEE b,
     PROJECT c,
     PROJECT d
where a.dnumber = c.dnum
  and a.mgr_ssn = b.ssn
  and a.dnumber = d.dnum
group by a.dname, 
         b.fname, 
         c.pname, 
         c.dnum;

Upvotes: 0

Stefano Zanini
Stefano Zanini

Reputation: 5926

First of all, that's not how you join tables, at least not since the early nineties.

Then, you want a single row for each department and manager, regardless of how many projects they have associated; this means you need to group by those two fields. The other information is the aggregation of the projects rows, and the aggregation you need is, as you said, the count.

Your final query should look like this

select  a.dname as "Departement",
        b.fname as "Manager",
        count(distinct c.pname) as "ProjectsCount"
from    DEPARTMENT a
join    EMPLOYEE b
on      a.mgr_ssn = b.ssn
join    PROJECT c
on      a.dnumber = c.dnum
group by a.dname, b.fname

Upvotes: 1

Related Questions